Count number of unique values in a range

[excelmyexcel]

Hi,

Does anyone know any easy formula to count the number of UNIQUE values in a range of cells?

Note that the values include both numbers and text. Ideally, the solution could be implemented in a formula contained within a single cell.

Here is an example of the data and solution

ABC
ABC
123
DEF
DEF
456
456
456

Solution = 4

Thanks!

 

[xld]

Try

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

 

[excelmyexcel]

Wow. Great solution. Thanks!