Nov 02 2018 01:28 AM - edited Nov 02 2018 01:32 AM
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
Nov 02 2018 01:59 AM
Hi Dennis,
Better to use tables, with ranges formula could be
=SUMPRODUCT((A2:A2733<>"")/COUNTIF(A2:A2733,A2:A2733&""))
Nov 02 2018 02:50 AM
Thanks @Sergei Baklan 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
Nov 02 2018 03:10 AM
SolutionYes, 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)
Nov 02 2018 04:32 AM
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)))
Nov 02 2018 04:58 AM
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))
Nov 02 2018 03:10 AM
SolutionYes, 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)