The unique count formula used here was I believe first created as sum array
formula by
former MVP David Hager

=SUM(1/COUNTIF(A1:A100,A1:A100))


after that some things have been added to it with sumproduct etc but the
basic idea is the 1/COUNTIF(Range.Range)

There was another formula used at the same time using FREQUENCY




--
Regards,

Peo Sjoblom





"Ron Coderre" wrote in message
...
First...to give credit where credit is due:
That formula was developed by Aladin Akyurek and Harlan Grove.

Within that formula's context
(A1:A10<"") checks if a cell value does not equal an empty string.
It returns a 1 for non-blanks and a 0 for blanks.

In the COUNTIF(A1:A10,A1:A10&"") section, this part: A2:A10&"" ensures
that
the COUNTIF function will always return at least 1 and never 0 (which
would
cause the formula to error out when the division is performed.)

Consequently, the numerator for blank cells is 0, so they are not counted.
The numerator for all non-blanks is 1.
The denominator for all non-blanks is their count.

If a value appears 3 times, three of the numerators will be 1 and their
respective denominators will be 3. The SUMPRODUCT function will add those
three fractions: (1/3)+(1/3)+(1/3)=1
That's how the three occurrences only count as a single instance of a
unique
value.

***********
Regards,
Ron

XL2003, WinXP


"PCLIVE" wrote:

Ron,

I'm trying to understand your formula.
For some reason, using the data provided by the OP, I get a #DIV/0!
error.
However, the COUNTIF portion gives the correct answer when used by itself
without the SUMPRODUCT.
Also, what purpose is served by the [&""] at the end? It seems to give
the
same answer with or without that.

Thanks,
Paul


--

"Ron Coderre" wrote in message
...
Try something like this:

=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))

Adjust the range reference to suit your situation

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"James" wrote:

I have a worksheet that lists names in one column. Is there a formula
that'll
calculate the number of people represented? For example, column A
lists:

Fred
Tony
Fred
Fred
Max

The five cells represent 3 people. I'm looking for a formula that'll
tell
me
the list represents 3 people.

Thanks for your help!