Count of Unique values

[Richard]

I have a list of values down a column, e.g.

a
a
a
c
c
e
e

etc.

I know I can use advanced filter to get a list of unique values and
then count them, but is there a single function that would achieve
this.

i.e the result for the data above would be 3 (a,c & e)

Rgds

 

[kk]

Hi Richard,

Try...

=SUMPRODUCT((YourRange<>"")/COUNTIF(YourRange,YourRange&""))




I have a list of values down a column, e.g.

a
a
a
c
c
e
e

etc.

I know I can use advanced filter to get a list of unique values and
then count them, but is there a single function that would achieve
this.

i.e the result for the data above would be 3 (a,c & e)

Rgds

 

[KL]

Hi Richard,

If you have numeric values then:

=SUM(N(FREQUENCY(A1:A10,A1:A10)>0))

If you have text or mixture of text and numeric and no blanks then:

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

If you have text or mixture of text and numeric and blank cells then:

=SUMPRODUCT(IF(ISNUMBER(1/COUNTIF(A1:A10,A1:A10)),1/COUNTIF(A1:A10,A1:A10)))
This one has to be ARRAY-entered (Ctrl+Shift+Enter, not just Enter). Blanks
are not counted.

Rgards,
KL

 

[Aladin Akyurek]

Hi Richard,

If you have numeric values then:

=SUM(N(FREQUENCY(A1:A10,A1:A10)>0))

If you have text or mixture of text and numeric and no blanks then:

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

If you have text or mixture of text and numeric and blank cells then:

=SUMPRODUCT(IF(ISNUMBER(1/COUNTIF(A1:A10,A1:A10)),1/COUNTIF(A1:A10,A1:A10)))
This one has to be ARRAY-entered (Ctrl+Shift+Enter, not just Enter). Blanks
are not counted.

The last one won't succeed with formula blanks, while

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

or

{=SUM(IF(A1:A10<>"",1/COUNTIF(A1:A10,A1:A10)))}

will.


BTW, whenever you need filtering with IF, it's better to replace
SumProduct with Sum.