Posted to microsoft.public.excel.worksheet.functions
|
|
Count Unique Values in a entire column, not just range
Nope, only takes a range argument.
That's great, thanks Biff!
--
Domenic
http://www.xl-central.com
In article ,
"T. Valko" wrote:
It returns #VALUE! Or is this because the VBA code
is not compatible with my Mac computer?
Nope, only takes a range argument.
COUNTDIFF will take conditionals.
Array entered:
=COUNTDIFF(IF(B2:B65536=2008,A2:A65536),FALSE,FALS E)
Didn't time this but the "eyeball test" says it's still very fast.
--
Biff
Microsoft Excel MVP
"Domenic" wrote in message
...
In addition, it doesn't look like COUNTU accepts a conditional
statement, such as...
=COUNTU(IF(A2:A100="X",B2:B100))
It returns #VALUE! Or is this because the VBA code is not compatible
with my Mac computer?
--
Domenic
http://www.xl-central.com
In article ,
"T. Valko" wrote:
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
|