• 460K Members
• 9,544 Online
• 558K Conversations
SOLVED

## Count unique values in a endless column

Occasional Contributor

# 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

5 Replies

# Re: Count unique values in a endless column

Hi Dennis,

Better to use tables, with ranges formula could be

`=SUMPRODUCT((A2:A2733<>"")/COUNTIF(A2:A2733,A2:A2733&""))`

# Re: Count unique values in a endless column

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

Solution

# Re: 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)

# Re: Count unique values in a endless column

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)))`

# Re: Count unique values in a endless column

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))`

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies