counting the # of unique values

[Guest]

I have a file with multiple worksheets. I need to find the # of unique values
in D. The values in D are alpha numeric

Ex.
Values
1a
1d
2e
1a

That should return the # "3" in the destination cell.

Thanks in advance.

 

[T. Valko]

You can't use entire columns as references with either of these formulas:
(in versions of Excel prior to Excel 2007)

If there will never be any empty/blank cells within the range:

=SUMPRODUCT(1/COUNTIF(D14,D14))

If there might be empty/blank cells within the range:

=SUMPRODUCT((D14<>"")/COUNTIF(D14,D14&""))

Biff

 

[Guest]

=SUM(IF(FREQUENCY(MATCH(A1:A4,A1:A4,0),MATCH(A1:A4,A1:A4,0))>0,1))

ctrl+shift+enter, not just enter

 

[T. Valko]

Which formula did you want to use?

Try this:

=SUMPRODUCT((D165535<>"")/COUNTIF(D165535,D165535&""))+(D65536<>"")*(ISNA(MATCH(D65536,D165535,0))

I hope you have a "strong" machine! That could take a while to calculate!

Biff