How do I count distinct names?
 

How do you count distinct names in a range? If a name is repeated in that
range, it should only count it as 1 name. For example:

apples, apples, orange, orange, pear, pear, pear, banana

the formula should return 4.

Thanks.

How do I count distinct names?
 

Try something like this:

For a list of values in A1:A10

B1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
.....confirmed with ENTER only, or...

B1: =SUM(IF(A1:A10<"",1/COUNTIF(A1:A10,A1:A10)))
.....confirmed with CONTROL+SHIFT+ENTER.


Does that help?

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

XL2002, WinXP-Pro


"Rona" wrote:

How do you count distinct names in a range? If a name is repeated in that
range, it should only count it as 1 name. For example:

apples, apples, orange, orange, pear, pear, pear, banana

the formula should return 4.

Thanks.

How do I count distinct names?
 

Hi!

Try this:

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

Biff

"Rona" wrote in message
...
How do you count distinct names in a range? If a name is repeated in that
range, it should only count it as 1 name. For example:

apples, apples, orange, orange, pear, pear, pear, banana

the formula should return 4.

Thanks.

How do I count distinct names?
 

COUNTIF(A1:A8,A1:A8&"")

What is the purpose of adding &"" ?

How do I count distinct names?
 

It prevents an error from being generated if there are empty cells *within*
the range.

Try this little experiment:

A1 = A
A2 = A
A3 = B
A4 = C
A5 = A

Enter this formula in B1 and copy down to B5:

=A1<""

Enter this formula in C1 and copy down to C5:

=COUNTIF(A$1:A$5,A1)

Enter this formula in D1 and copy down to D5:

=B1/C1

Enter this formula in E1:

=SUM(D1:D5)

One thing you'll notice is the sum = 3 when you might think it should be
2.9999999999999999999~.

Now, try this:

Clear the contents of cell A3. See what happened?

Now, try this:

Change the formula in C1 and copy down to C5:

=COUNTIF(A$1:A$5,A1&"")

See what the &"" does?

Now, for something really strange that I can't really explain (although I
have an idea)

Insert a new sheet (or try this on a sheet that has not been used yet)

Enter some stuff in a few cells:

A1 = A
A2 = A
A3 = B

Now enter this formula in B1:

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

Did you get a #DIV/0! error?

Now, enter something in A10. The #DIV/0! error disappears! (as expected)

Now, clear the contents of cell A10.

Biff

wrote in message
oups.com...
COUNTIF(A1:A8,A1:A8&"")

What is the purpose of adding &"" ?

How do I count distinct names?
 

Biff wrote:
Hi!

Try this:

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


Are you not coercing twice?

Since division must occur anyway,

=SUMPRODUCT(--(A1:A8<""),1/COUNTIF(A1:A8,A1:A8&""))

won't be that seducing.

How do I count distinct names?
 

Needed in order to eliminate empty cells and cells with formula blanks
as distinct types...

See:

http://www.mrexcel.com/board2/viewto...ighlight=token
http://www.mrexcel.com/board2/viewto...ighlight=hager
http://www.mrexcel.com/board2/viewto...ighlight=grove
wrote:
COUNTIF(A1:A8,A1:A8&"")

What is the purpose of adding &"" ?

How do I count distinct names?
 

It worked. This was a brilliant formula. Thank you very much. You must be a
mathematician or a programmer. Thanks again.

"Ron Coderre" wrote:

Try something like this:

For a list of values in A1:A10

B1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
....confirmed with ENTER only, or...

B1: =SUM(IF(A1:A10<"",1/COUNTIF(A1:A10,A1:A10)))
....confirmed with CONTROL+SHIFT+ENTER.


Does that help?

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

XL2002, WinXP-Pro


"Rona" wrote:

How do you count distinct names in a range? If a name is repeated in that
range, it should only count it as 1 name. For example:

apples, apples, orange, orange, pear, pear, pear, banana

the formula should return 4.

Thanks.

How do I count distinct names?
 

You're very kind, but the true credit for that formula goes to Aladin Akyurek
and Harlan Grove. I'm sure they appreciate your praise of their fine work.

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

XL2002, WinXP-Pro


"Rona" wrote:

It worked. This was a brilliant formula. Thank you very much. You must be a
mathematician or a programmer. Thanks again.

"Ron Coderre" wrote:

Try something like this:

For a list of values in A1:A10

B1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
....confirmed with ENTER only, or...

B1: =SUM(IF(A1:A10<"",1/COUNTIF(A1:A10,A1:A10)))
....confirmed with CONTROL+SHIFT+ENTER.


Does that help?

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

XL2002, WinXP-Pro


"Rona" wrote:

How do you count distinct names in a range? If a name is repeated in that
range, it should only count it as 1 name. For example:

apples, apples, orange, orange, pear, pear, pear, banana

the formula should return 4.

Thanks.