software

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:A10lt;gt;quot;quot;)/COUNTIF(A1:A10,A1:A10amp;quot;quot;))
.....confirmed with ENTER only, or...

B1: =SUM(IF(A1:A10lt;gt;quot;quot;,1/COUNTIF(A1:A10,A1:A10)))
.....confirmed with CONTROL SHIFT ENTER.Does that help?

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

XL2002, WinXP-Proquot;Ronaquot; wrote:

gt; How do you count distinct names in a range? If a name is repeated in that
gt; range, it should only count it as 1 name. For example:
gt;
gt; apples, apples, orange, orange, pear, pear, pear, banana
gt;
gt; the formula should return 4.
gt;
gt; Thanks.

Hi!

Try this:

=SUMPRODUCT(--(A1:A8lt;gt;quot;quot;)/COUNTIF(A1:A8,A1:A8amp;quot;quot;))

Biff

quot;Ronaquot; gt; wrote in message
...
gt; How do you count distinct names in a range? If a name is repeated in that
gt; range, it should only count it as 1 name. For example:
gt;
gt; apples, apples, orange, orange, pear, pear, pear, banana
gt;
gt; the formula should return 4.
gt;
gt; Thanks.
COUNTIF(A1:A8,A1:A8amp;quot;quot;)

What is the purpose of adding amp;quot;quot; ?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:

=A1lt;gt;quot;quot;

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(D15)

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,A1amp;quot;quot;)

See what the amp;quot;quot; 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:A10lt;gt;quot;quot;)/COUNTIF(A1:A10,A1:A10amp;quot;quot;))

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

gt; wrote in message oups.com...
gt; COUNTIF(A1:A8,A1:A8amp;quot;quot;)
gt;
gt; What is the purpose of adding amp;quot;quot; ?
gt;


Biff wrote:
gt; Hi!
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT(--(A1:A8lt;gt;quot;quot;)/COUNTIF(A1:A8,A1:A8amp;quot;quot;))
gt;

Are you not coercing twice?

Since division must occur anyway,

=SUMPRODUCT(--(A1:A8lt;gt;quot;quot;),1/COUNTIF(A1:A8,A1:A8amp;quot;quot;))

won't be that seducing.
Needed in order to eliminate empty cells and cells with formula blanks
as distinct types...

See:

www.mrexcel.com/board2/viewto...ighlight=token
www.mrexcel.com/board2/viewto...ighlight=hager
www.mrexcel.com/board2/viewto...ighlight=grovewrote:
gt; COUNTIF(A1:A8,A1:A8amp;quot;quot;)
gt;
gt; What is the purpose of adding amp;quot;quot; ?
gt;

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

quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; For a list of values in A1:A10
gt;
gt; B1: =SUMPRODUCT((A1:A10lt;gt;quot;quot;)/COUNTIF(A1:A10,A1:A10amp;quot;quot;))
gt; ....confirmed with ENTER only, or...
gt;
gt; B1: =SUM(IF(A1:A10lt;gt;quot;quot;,1/COUNTIF(A1:A10,A1:A10)))
gt; ....confirmed with CONTROL SHIFT ENTER.
gt;
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Ronaquot; wrote:
gt;
gt; gt; How do you count distinct names in a range? If a name is repeated in that
gt; gt; range, it should only count it as 1 name. For example:
gt; gt;
gt; gt; apples, apples, orange, orange, pear, pear, pear, banana
gt; gt;
gt; gt; the formula should return 4.
gt; gt;
gt; gt; 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-Proquot;Ronaquot; wrote:

gt; It worked. This was a brilliant formula. Thank you very much. You must be a
gt; mathematician or a programmer. Thanks again.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; For a list of values in A1:A10
gt; gt;
gt; gt; B1: =SUMPRODUCT((A1:A10lt;gt;quot;quot;)/COUNTIF(A1:A10,A1:A10amp;quot;quot;))
gt; gt; ....confirmed with ENTER only, or...
gt; gt;
gt; gt; B1: =SUM(IF(A1:A10lt;gt;quot;quot;,1/COUNTIF(A1:A10,A1:A10)))
gt; gt; ....confirmed with CONTROL SHIFT ENTER.
gt; gt;
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Ronaquot; wrote:
gt; gt;
gt; gt; gt; How do you count distinct names in a range? If a name is repeated in that
gt; gt; gt; range, it should only count it as 1 name. For example:
gt; gt; gt;
gt; gt; gt; apples, apples, orange, orange, pear, pear, pear, banana
gt; gt; gt;
gt; gt; gt; the formula should return 4.
gt; gt; gt;
gt; gt; gt; Thanks.