Different Names Count

[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

 

[JE McGimpsey]

One way:

A13: =SUMPRODUCT((A1:A12<>"")/COUNTIF(A1:A12,A1:A12&""))

 

[anonymous]

Brilliant!

Can you explain how the countif works?

 

[JE McGimpsey]

Try this:


In C1:C12, array enter

=A1:A12<>""

In D112, array enter

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

and in E1:E12, array-enter:

=C1:C12/D112

see what happens?

 

[anonymous]

see what happens?

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

Thanks.

 

[anonymous]

see what happens?

Ok, but then why isn't the returned value 8.999999999~

 

[anonymous]

I still can't figure out what &"" does. If I remove it,
the formula still works.

 

[Dave Peterson]

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

 

[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?

 

[Dave Peterson]

My guess is that it's a rounding error.

J.E. has some notes that explain this at:
http://www.mcgimpsey.com/excel/pennyoff.html

 

[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.