MicrosoftSo I'm having trouble understanding why the below doesn't work.
I have an Excel table (just called Table1) with one column called "Number". In it are 6 item numbers.
Number
| a123456 |
| a123444 |
| a000013 |
| a004025 |
| a004023 |
| a123457 |
I have the following formula in E2: =LEFT(Table1[Number],6)
Then I have another formula in F2: =COUNTIFS($E$2#,LEFT(Table1[@Number],6))
That formula I copy down from F2:F7. Works perfectly. It tells me if the first 6 digits of the 7 digit item numbers are repeated. The numbers a123456 and a123457 would show repeats because the first 6 digits, a12345, are repeated 2x in the list.
However, if I replace the $E$2# reference with the LEFT(Table1[Number],6) function, Excel tells me there is an error with the formula and won't let me enter it.
Is there any way with Dynamic Arrays to get this to be a single formula without this "helper formula" in E2#? I've tried COUNTIF() as well, and no matter what I do, I cannot get it to accept an array from a function, but it does from a #SPILL reference.
