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.

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.

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.

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.

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.

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

What is the purpose of adding &"" ?

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 &"" ?

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 &"" ?

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.