Counting Unique Data

[Drake]

Is there a way to count the unique number of items in a column?

If you have something like the following:

x
4
9
3c
21
q
16
Tom
x
q

There would be 8 unique items (q and x are each repeated twice.)

Thanks

 

[Dave Peterson]

One way:

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

 

[shaqil]

One way:

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

THE OTHER WAY
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))

 

[Dave Peterson]

This'll have trouble if there are empty cells in the range.

shaqil wrote: