Forum Discussion
Count unique values in a endless column
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 SergeiBaklan for your quick response!!!!
I tested it on a sheet with only 10 rows: works perfect
I tested it on a sheet with the 2733 rows: returns 0. When I saved the file he returns the correct value after a while (computer works slow for a minute of 2). But when I entered the cell, he returns 0 again.
My idea is that this is a very "complex" formula that need a lot of calculation power (compare every row with every row = 2733^2733 = 7,469,289 compares. I don't know how he does it with your formula, but I think he goes till the end of the workbook = a lot of rows ^ a lot of rows = to many rows
The fact that I need that formula a few times in the same sheet,... this will be a disaster. Is there any other way to calculate this so he don't go till the end of the workbook until it's not necessary?
Best,
Dennis
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))