Different Names Count

R

Ravi Sandhu

Hi guys

Hope someone can help

Here is an example of what I am trying to do

Cell range A1 - A12

Bob
James
Jill
George
Tracy
Mike
Bob
Philip
Graham
Jack
James
Bob

Different names = 9

Ignore repeated names, except once


What formula would I need to put in cell A13 to give me this figure?

Thank you in advanced


Ravi Sandhu
 
J

JE McGimpsey

Try this:


In C1:C12, array enter

=A1:A12<>""

In D1:D12, array enter

=COUNTIF(A1:A12,A1:A12&"")

and in E1:E12, array-enter:

=C1:C12/D1:D12

see what happens?
 
A

anonymous

see what happens?

Very slick! I'm gonna have to stash this one in my little
black book.

Thanks.
 
D

Dave Peterson

Try your formula (without the &"") and select A3 and hit edit|clear|contents.
 
A

anonymous

Ok, got it! Can you answer my other question? Based on the
OP's sample data and trying J.E.'s method of breaking the
formula down into the 3 steps the array of values passed
to the sumproduct function total 8.9999999 but the
returned value is 9. Why?
 
A

anonymous

My guess is that it's a rounding error.

But in this case, it looks like an error that can be taken
advantage of!

Thanks! When I see a different solution I like to
understand how it works rather than just knowing that it
does work.
 
Top