Forum Discussion
Solved
Count unique values in a endless column
Hi All, I'm stuck :) I like to count the amount of unique values in a column. So far no problem with the following formula: ={SUMPRODUCT(1/COUNTIF(A2:A2733,A2:A2733))} This gives me the ...
Yes, COUNTIF could be quite slow on big range. FREQUENCY shall be faster
=SUM(IF(FREQUENCY(IF(A2:A35000<>"",MATCH(A2:A35000,A2:A35000,0)),ROW(A2:A35000)-ROW($A$2)+1),1))
Above is array formula (Ctrl+Shift+Enter)
Yes, COUNTIF could be quite slow on big range. FREQUENCY shall be faster
=SUM(IF(FREQUENCY(IF(A2:A35000<>"",MATCH(A2:A35000,A2:A35000,0)),ROW(A2:A35000)-ROW($A$2)+1),1))
Above is array formula (Ctrl+Shift+Enter)
Thanks for the formula,... works like a charm!!!
Can you explain the formula a little bit more to me what it does? Because I need to at extra conditions to it (by example =if(F:F = "Active", SUM(...))
But i can only do that if I understand the formula better, because the formula below don't work :)
=IF(B:B="Active", SUM(IF(FREQUENCY(IF(A2:A35000<>"",MATCH(A2:A35000,A2:A35000,0)),ROW(A2:A35000)-ROW($A$2)+1),1)))
Dennis, please check this article https://exceljet.net/formula/count-unique-numeric-values-with-criteria which explains how it works. It uses more compact variant of the formula, in your case it'll be like
=SUM(--(FREQUENCY(IF((A2:A35000<>"")*(B2:B35000="Active"),A2:A35000),A2:A35000)>0))