Count Unique with condition

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AMDRIT - 10 Jul 2007 15:45 GMT

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

Reply to this Message

Bernie Deitrick - 10 Jul 2007 16:32 GMT

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

> Hello Everyone,
>
[quoted text clipped - 19 lines]
>
> Thanks in advance

Reply to this Message

AMDRIT - 10 Jul 2007 16:46 GMT

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

> AMDRIT,
>
[quoted text clipped - 33 lines]
>>
>> Thanks in advance

Reply to this Message

AMDRIT - 10 Jul 2007 17:07 GMT

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

> Thanks Bernie, it works like a charm! I was hung up on trying to
> concatenate or, ... well all kinds of nonesense.
[quoted text clipped - 36 lines]
>>>
>>> Thanks in advance

Reply to this Message

AMDRIT - 10 Jul 2007 17:23 GMT

I found my issue,

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

> 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
[quoted text clipped - 43 lines]
>>>>
>>>> Thanks in advance

Reply to this Message

Bernie Deitrick - 10 Jul 2007 17:07 GMT

> 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

Reply to this Message

Rate this thread: