If the OP wants the most efficient method they *should* use the Morefunc
add-in. Not only is it the most efficient method you also get a library of
useful functions.

I tested the COUNTU function against the COUNTDIFF function (using Charles
Williams RangeTimer method).

Filled A2:A65536 with =ROW(). 65,535 unique entries. Average calculation
time (5 calculations):

COUNTU(A2:A65536) = 1.42 secs
COUNTDIFF(A2:A65536) = 0.04 secs

Filled A2:A65536 with random numbers from 0 to 100. 101 unique entries.
Average calculation time (5 calculations):

COUNTU(A2:A65536) = 0.38 secs
COUNTDIFF(A2:A65536) = 0.07 secs

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Hello,

I suggest NOT to use the SUMPRODUCT divided by COUNTIF approach
(please see entry 3 of my Excel Dont's:
http://www.sulprobil.com/html/excel_don_ts.html
) and NOT to use the Morefunc addin (entry 1 of that same list) but to
take Charles Williams' COUNTU function.

Please find my analysis on this he
http://www.sulprobil.com/html/count_unique.html

Regards,
Bernd