In Excel - How to count unique values in a range

  • Thread starter Thread starter Giles
  • Start date Start date
G

Giles

How do I do this?

for example - if I have a range of values, how do I count
the number of unique values that appear

for example

the range A1:A11 contains the values
1,2,1,3,4,3,2,5,4,2,1 the number of unique values here is
5
the formula must exist somewhere and should look like

=countunique(A1:A11,1) results in 5
I suppose the second part of the field would be to
include Nulls as unique or not.

Help
 
One way

=SUMPRODUCT(1/COUNTIF(A1:A11,A1:A11))

if blanks are involved use

=SUM(IF(LEN(A1:A11)>0,1/COUNTIF(A1:A11,A1:A11)))

entered with ctrl + shift & enter
 
Back
Top