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!

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!

Yes! That works great. Thank you!!

"Ron Coderre" wrote:

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!

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!

reproducing my answer of a couple of days ago ...

Let's start with a list that is being counted in A1:A10.

Bob,John,Bob,Bob,John,John,Bob,Bill,Bill,Max

The basic formula to count unique items is

=SUMPRODUCT(1/COUNTIF($A$1:$A$10,$A$1:$A$10))

The COUNTIF($A$1:$A$10,$A$1:$A$10) part of the formula builds an array of
the number of occurrences of each item, in this case{4;3;4;4;3;3;4;2;2;1}.
As can be seen, each occurrence of the repeated value is counted, so there
are four occurrences of Bob in the array. There will always be the same
number of occurrences of value as the count of that value, unless two or
more items are repeated the same number of times, in which case it will be
some multiple of that count.

Thus the item that is repeated 4 times has 4 instances of that count,
dividing 1 by the count of 4, gives 0.25 4 times. The full array of values
is

{0.25;0.333333333333333;0.25;0.25;0.33333333333333 3;0.333333333333333;0.25;0
..5;0.5;1}.

The item that repeats 4 times sums to 1. The item that repeats 3 times also
sums to 1. It should be clear from this that every value works in the same
way and sums to 1. In other words, 1 is returned for every unique item. The
sum of these values becomes the count of unique items.

As our test range is A1:A20, and some of the items in A1:A20 are blank,
extending this formula to A1:A20 would return a #DIV/0! Error.

The reason for the error is blank cells in the full range A1:A20. Each blank
cell returns a 0 value from the COUNTIF formula, which gives the #DIV/0!
Error when divided into 1.

The solution to this is to force it to count the empty cells as well, and
not return a zero. Adding &"" to the end of the COUNTIF formula forces a
count of the blanks.

This addition on its own removes the #DIV/0! error, but will cause the
blanks to be counted as a unique item. A further addition to the formula
resolves this by testing for those blanks. Instead of dividing the array of
counts into 1 each time, adding the test creates an array of TRUE/FALSE
values to be divided by the equivalent element in the counts array. Each
blank will resolve to FALSE in the dividend array, and the count of the
blanks in the divisor array. The result of this will be 0, so the blanks do
not get counted.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

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

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!

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!

Thank you both, Ron and Bob, for the detailed explanations. After reading
your posts, I figured out that the reason the COUNTIF function, alone,
appeared to be producing the correct answer was because there wasn't enough
data. When I added one more name, I then got the wrong result.

Thanks for helping me understand.
Regards,
Paul

--

"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!

To confirm, I have a copy of an article from something called "Inside
Microsoft Office 95" dated September 1996 that covers a formula for
counting unique value. A footnote states: "The formula presented
in this article came from David Hagar, ..."
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



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

Here are a few different ways of doing the same thing:

=SUMPRODUCT((A2:A998<"")/(COUNTIF(A2:A998,A2:A998)+(A2:A998="")))
=SUM(IF(FREQUENCY(IF(LEN(A2:A999)0,MATCH(A2:A999, A2:A999,0),""),IF(LEN(A2:A999)0,MATCH(A2:A999,A2: A999,0),""))0,1))

=SUMPRODUCT((A2:A105<"")/COUNTIF(A2:A105,A2:A105&"")*(A2:A105<""))
=SUMPRODUCT(--(A2:A999<""),1/COUNTIF(A2:A999,A2:A999&""))
=SUM(--(FREQUENCY(IF(A2:A2705<"",MATCH(A2:A2705,A2:A2705 ,0)),ROW(INDIRECT("1:"&ROWS(A2:A2705))))0))

***watch out for word wrap***

Sometimes I use a few different functions, which do the same thing, just to
confirm that the answer I get is the correct answer. It is highly unlikely
that you will make a mistake a few times, and get exactly the same results.
There are lots of spreadsheets being used for lots of purposes, and many out
there contain mistakes. Caution, and overall good judgment, cant be
overstated.

Ryan---


"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!