Count Unique with condition

[AMDRIT]

Hello Everyone,

I am looking for a formula to count unique occurances of column (A) where
column (B) equals a certain value. Any help would be appreciated.

I have the formula
=SUMPRODUCT(('data'!A3:A6<>"")/COUNTIF('data'!A3:A6,'data'!A3:A6 &"")),
which gives me a count of the unique values in 'data'!A, now I would like to
break it down over a value in 'data'!B

the result would look like this:

smith, john, blue
smith, john, red
jones, james, yellow
jones, james, green
williams, john, orange
williams, john, teal

Unique john = 2
Unique james = 1
Unique count = 3


Thanks in advance

 

[Bernie Deitrick]

AMDRIT,

=SUMPRODUCT((data!B3:B6="john")/COUNTIF(data!A3:A6,data!A3:A6&""))

or if john is in cell C3, and you have a list of names, this will allow you to copy down:

=SUMPRODUCT((data!$B$3:$B$6=C3)/COUNTIF(data!$A$3:$A$6,data!$A$3:$A$6&""))

HTH,
Bernie
MS Excel MVP

 

[AMDRIT]

Thanks Bernie, it works like a charm! I was hung up on trying to
concatenate or, ... well all kinds of nonesense.

 

[AMDRIT]

So now my question is, are the results then relative? I am getting results
of 234.5 and 567.5 and 15.0 ... , certainly I can round but is that the
correct result?


Thanks again

 

[Bernie Deitrick]

Thanks Bernie, it works like a charm! I was hung up on trying to concatenate or, ... well all

kinds of nonesense.

That's what we like to do - cut through the nonsense... ;-)

HTH,
Bernie
MS Excel MVP

 

[AMDRIT]

I found my issue,

on an occasion, john becomes james, so I assume I should take the floor of
the result.

 

[G J]

Ok,

I have a similar requirement, but need to get data based on uniqueness in 2 fields and not one.

If we look @ the example, I want the count of colors for the unique full names

I guess, I need an array formula, but want to be sure of how

 

[Stan Brown]

Ok,

I have a similar requirement, but need to get data based on uniqueness in 2 fields and not one.

If we look @ the example, I want the count of colors for the unique full names

I guess, I need an array formula, but want to be sure of how
....

Sigh. Yet another Egghead Cafe idiot resurrecting a three-year-old
thread, *and* changing the subject line so no one can make sense of
it.

I'm done. Everything from Egghead Cafe now joins everything from
Google Groups in my automatic kill file. If you can't use a proper
news client, and can't be bothered to follow basic netiquette, you're
not worth listening to.