function to return "number" of different values that occur ?

[scott]

Hi,

Is there an function is Excel that can return the number of different values
i.e

COLUMNA
1
1
1
1
2
2
3
4
5
6
6
6
6
6

TOTAL = 6
(i.e 6 different values appear)

Thanks for any help.
Scott

 

[Frank Kabel]

Hi
=SUMPRODUCT((A1:A100<>"")/(COUNTIF(A1:A100,A1:A100)+(A1:A100="")))

 

[scott]

that was quick.

cheers
scott