How do I count distinct names?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Biff said:
Hi!

Try this:

=SUMPRODUCT(--(A1:A8<>"")/COUNTIF(A1:A8,A1:A8&""))
Click to expand...

Are you not coercing twice?

Since division must occur anyway,

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

won't be that seducing.
 
It worked. This was a brilliant formula. Thank you very much. You must be a
mathematician or a programmer. Thanks again.
 
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
 
Back
Top