Forum Discussion
Hey!
If combine mtarler formulas in one
=FILTER(IF({1,0},SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1))), COUNTIFS($A$2:$A$1500,SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1)))&"?")), COUNTIFS($A$2:$A$1500,SORT(UNIQUE(LEFT($A$2:$A$1500,LEN($A$2:$A$1500)-1)))&"?") >0)
TheAntony , not exactly like that, in the same column same numbers with different letters.
like
1r 1b 1c 1g
2e 2f 2g
then the count will be total 3 times 2
and so on with 3-99999
and than the count will count all the same numbers with different letters.
killerBee615 i think I understand what you want. you want to know how many different numbers there are in the column if you ignore the letters.
The best I got is if you create a helper column using this formula to strip off the letters:
=LEFT(A1,SUM(--ISNUMBER(--MID(A1,{1,2,3,4,5},1))))but that assumes a) a maximum of 99999 (i.e. 5 digits) and b) there are no numbers after the letters start. Other versions of this formula are possible if needed.
Once you have this 'helper' column then you can simply use:
=COUNTA(UNIQUE(B:B))to count how many unique numbers exist in that helper column.