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!

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

wrote in message
ps.com...
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!

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

..o.

On May 25, 11:41 am, "T. Valko" wrote:
Try this array formula**

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

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

Biff

You're welcome. Thanks for the feedback!

Biff

wrote in message
oups.com...
Jackpot! I was way off, mucking around with various SUMPRODUCT
arrays... Thanks!

..o.

On May 25, 11:41 am, "T. Valko" wrote:
Try this array formula**

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

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

Biff

You could have done it w/sumproduct

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


" wrote:

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

..o.

On May 25, 11:41 am, "T. Valko" wrote:
Try this array formula**

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

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

Biff

JMB wrote...
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))

Another nice one.

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

Biff

"Harlan Grove" wrote in message
oups.com...
JMB wrote...
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))

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

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

"Harlan Grove" wrote:

JMB wrote...
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))

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

ctrl+shift+enter, not just enter


" wrote:

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!