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

[Guest]

I would like to know how many unique values I have in a given list / range.

Is there a formula to return this number. The data is generally alphanumeric.

Thanks
Sean

 

[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.

Many thanks in advance!

 

[Aladin Akyurek]

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.

Many thanks in advance!

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

 

[chiefcrazybull]

Thanks, that works perfectly.

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

 

[Aladin Akyurek]

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:

http://www.mrexcel.com/forum/showthread.php?t=292473

 

[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

 

[Aladin Akyurek]

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?

 

[bolo]

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.

 

[bolo]

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

Hi Aladin,

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?

 

[Aladin Akyurek]

Hi Aladin,

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...

http://www.mrexcel.com/forum/showthread.php?t=36118

http://www.mrexcel.com/forum/showthread.php?t=16682

http://www.mrexcel.com/forum/showthread.php?t=70835

Also, regarding efficiency issues...

http://www.mrexcel.com/forum/showthread.php?t=292473

 

[bolo]

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

Thanks for the explanations Aladin.