counting the # of unique values

B

bobby769

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

Ex.
Values
1a
1d
2e
1a

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

Thanks in advance.
 
T

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(D1:D4,D1:D4))

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

=SUMPRODUCT((D1:D4<>"")/COUNTIF(D1:D4,D1:D4&""))

Biff
 
T

Teethless mama

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

ctrl+shift+enter, not just enter
 
T

T. Valko

Which formula did you want to use?

Try this:

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

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

Biff
 
Top