Count Distinct?

[SB]

Does anyone know if there is a formula for count
distinct? I can't seem to find anything in the help on
it.
Thanks.

 

[Jason Morin]

One way:

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

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

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

 

[brendon]

Jason, I try using the formula, but all I get is the
#DIV/0!. Am I doing something wrong.

 

[Harlan Grove]

Jason, I try using the formula, but all I get is the
#DIV/0!. Am I doing something wrong.

....

You likely have some blank cells in the range. If so, use either of the
following instead.

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

=SUMPRODUCT((1-ISBLANK(A1:A100))/(COUNTIF(A1:A100,A1:A100)
+ISBLANK(A1:A100)))

The first excludes blank cells and cells evaluating to "". The second
excludes only blank cells.