Thanks:  0
Likes:  0

# Thread: How do you Count the number of unique values in a list ?

1. ## Re: How do you Count the number of unique values in a list ?

Hi, further to this thread from some time ago, I have an additional requirement of a formula based on the one issued by Aladin Akyurek =IF(LEN(A1:A6),SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6)))

I want to know how many times, any values in a range are repeated a certain number of times. I'm going round in circles trying to solve this.

For example, in a column of cells containing say 1,1,1,1,2,2,2,2,3,3,3,3,4,4,5,5,6,6,6 then the formula above will return 6, as there are 6 unique values. What I want to do is know how many any values appear say 4 times (the reply would be 3, as 1,2 and 3 all appear 4 times). I realise a pivot table would be the easiest way of achieving this, but I really would like to accomplish it via formula instead.

2. ## Re: How do you Count the number of unique values in a list ?

Originally Posted by chiefcrazybull
Hi, further to this thread from some time ago, I have an additional requirement of a formula based on the one issued by Aladin Akyurek =IF(LEN(A1:A6),SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6)))

I want to know how many times, any values in a range are repeated a certain number of times. I'm going round in circles trying to solve this.

For example, in a column of cells containing say 1,1,1,1,2,2,2,2,3,3,3,3,4,4,5,5,6,6,6 then the formula above will return 6, as there are 6 unique values. What I want to do is know how many any values appear say 4 times (the reply would be 3, as 1,2 and 3 all appear 4 times). I realise a pivot table would be the easiest way of achieving this, but I really would like to accomplish it via formula instead.

Let A2:A20 house the example that you mention and C2 4, the occurrence frequency that you require...

D2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A20<>"",MATCH("~"&A2:A20,A2:A20&"",0)),ROW(A2:A20)-ROW(A2)+1)=C2,1))

3. ## Re: How do you Count the number of unique values in a list ?

Thanks, that works perfectly.

I wont even try to pretend I understood most of that formula, but it works!

4. ## Re: How do you Count the number of unique values in a list ?

Originally Posted by chiefcrazybull
Thanks, that works perfectly.

I wont even try to pretend I understood most of that formula, but it works!
Although it does not provide explanations how the relevant formulas work, the following link might interest you:

5. ## Re: How do you Count the number of unique values in a list ?

Hi,

Is there a way to get this to work on non-contiguous cells (ie cell ranges that are not next to each other?)

Thanks!

Bolo

6. ## Re: How do you Count the number of unique values in a list ?

Originally Posted by bolo
Hi,

Is there a way to get this to work on non-contiguous cells (ie cell ranges that are not next to each other?)

Thanks!

Bolo
Hard to do. Do these cells/ranges have some regularity in lay-out, which could possibly be exploited?

7. ## Re: How do you Count the number of unique values in a list ?

Hard to do. Do these cells/ranges have some regularity in lay-out, which could possibly be exploited?
They do, but in the end i added a routine to make the ranges into one list first. But i was just wondering if it was possible using just one formula. I guess not! Thanks for your help.

8. ## Re: How do you Count the number of unique values in a list ?

You can also use the following formula to obtain the desired count of uniques items:

=IF(LEN(A1:A6),SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6)))

I know this thread is quite old, but i was wondering. What is the point of the if len part? THe formula seems to work ok without it in excel 2007. Is the if len part something needed to trapped an error in earlier versions of excel?

9. ## Re: How do you Count the number of unique values in a list ?

Originally Posted by bolo

I know this thread is quite old, but i was wondering. What is the point of the if len part? THe formula seems to work ok without it in excel 2007. Is the if len part something needed to trapped an error in earlier versions of excel?
If any cell in A1:A6 is empty or house a formula blank:

SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6))

SUM(1/COUNTIF(A1:A6,A1:A6)) [ Original formula; Requires control+shift+enter ]

both would yield a #DIV/0!

To circumvent that issue and not to count an empty cell or a blank as a distinct item, you'd need...

With the SumProduct version:

SUMPRODUCT((A1:A6<>"")/COUNTIF(A1:A6,A1:A6&""))

With the Sum Version:

SUM(IF(LEN(A1:A6),1/COUNTIF(A1:A6,A1:A6))) [ Requires control+shift+enter ]

of which the shorter version is:

SUM(IF(A1:A6<>"",1/COUNTIF(A1:A6,A1:A6)))

Here are some links with explanations...

Also, regarding efficiency issues...

10. ## Re: How do you Count the number of unique values in a list ?

so the original formula was missing the outside sum!!! that is what confused me!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•