counting the # of unique values
 

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.

counting the # of unique values
 

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

"bobby769" wrote in message
...
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.

counting the # of unique values
 

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

ctrl+shift+enter, not just enter


"bobby769" wrote:

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.

counting the # of unique values
 

Which formula did you want to use?

Try this:

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

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

Biff

"bobby769" wrote in message
...
So what do I do if the entire column has data? Yes, its a very large file.
Is
my only option to upgrade to Excel '07?


"T. Valko" wrote:

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

"bobby769" wrote in message
...
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.