count unique instances based on two columns

[omnicrondelicious]

Hola internet excel gurus, need your help again. Here's a simplified
mock-up of the data that got dumped on my lap:

ColA | ColB
Bob | Yes
Bob | Yes
Beth | Yes
Beth | Yes
Beth | Yes

Phil | No
Phil | No
Phil | No
Jane | Pending


I need to count the number of uniques values in Column A who have a
'Yes' in Column B. So in the example above, I'm hoping to get "2" (Bob
Yes, Beth Yes). Note the empty row between Beth and Phil - there are
some empty cells in this data.

I feel like I'm dancing around the correct formula but can't seem to
wrap my head around it correctly. Thanks!

 

[T. Valko]

Try this array formula**

=COUNT(1/FREQUENCY(IF(status="Yes",MATCH(name,name,0)),ROW(name)-MIN(ROW(name)+1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[omnicrondelicious]

Jackpot! I was way off, mucking around with various SUMPRODUCT
arrays... Thanks!

..o.

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Jackpot! I was way off, mucking around with various SUMPRODUCT
arrays... Thanks!

..o.

 

[Guest]

You could have done it w/sumproduct

=SUMPRODUCT(--(MATCH(name&status,name&status,0)=ROW(name)-MIN(ROW(name))+1),--(status="Yes"))

 

[Guest]

=SUM(IF(FREQUENCY(IF(status="yes",MATCH(name&"",name&"",0)),MATCH(name&"",name&"",0))>0,1))

ctrl+shift+enter, not just enter

 

[Harlan Grove]

You could have done it w/sumproduct

=SUMPRODUCT(--(MATCH(name&status,name&status,0)
=ROW(name)-MIN(ROW(name))+1),--(status="Yes"))

....

Even easier with COUNT in the following array formula.

=COUNT(1/(MATCH(name&"Yes",name&status,0)=ROW(name)-MIN(ROW(name))+1))

 

[T. Valko]

Another nice one.

However, none of our formulas account for name = empty and status = yes (if
that'd even be a possibility).

Biff

 

[T. Valko]

Harlan, off topic...

I'd be interested in your thoughts on this:

http://tinyurl.com/yv4p8t

I'm having a hard time coming up with logic to determine if the range is
"mixed". Empty cells can be anywhere in the range.

Same = 1,1,1,1,1, ,1
Ascending = 1, ,1,2,5, ,6
Descending = 5,5,3, ,1,1,1
Mixed = 4,1, ,5,5,3,4

Testing for Same, Ascend and Descend is easy enough but I'm testing in this
order:

Same, Mixed, Ascend, if all others are FALSE, then Decend

Same: =MAX(FREQUENCY(B1:H1,B1:H1))=COUNTA(B1:H1)
Mixed: ???
Ascend: =SLOPE(B1:H1,COLUMN(B1:H1))>0
Descend: If not Same, If not Mixed, If not Ascend, then Descend

Biff

 

[Guest]

Yes - that would simplify it - appreciate your suggestions Harlan.