Forum Discussion
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 correct value.
But the problem is, I make a report, and every week I need to update the report with new data. So today there are 2733 rows, but tomorrow there will be more :)
So I thought:
={SUMPRODUCT(1/COUNTIF(A:A,A:A))}
RESULT: #DIV/0!
WHY: 1 divided with an empty cell = 1 / 0 = not possible
So I thought, lets build the formula up with a count of rows:
B1 =COUNTA(A:A) = 2733
={SUMPRODUCT(COUNTIF(INDIRECT("A1:A2733"&B1),INDIRECT("A1:A2733"&B1)))}
But again: RESULT: #DIV/0!
So now I'm stuck! I like to count the unique values in a column, but not knowing up front how many rows there will be... can someone help me out?
Thanks,
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)
5 Replies
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)))
Hi Dennis,
Better to use tables, with ranges formula could be
=SUMPRODUCT((A2:A2733<>"")/COUNTIF(A2:A2733,A2:A2733&""))
