count unique values based on a value of "Yes" in another column

[clagar1610]

Need help trying to count unique values based on a value of "Yes" in another column?

In column E I have a list of cities, used the following formula to get a count of unique cities -

SUM(IF(FREQUENCY(MATCH(E$8:E$172,E$8:E$172,0),MATCH(E$8:E$172,E$8:E$172,0))>0,1))

But now I also want to only count the unique city if Column G has a value of "Yes".

Please help?

 

[PietBom]

I would use a pivot table with filter on "Yes" and the use COUNTA-function to count the number of unique codes
k.r.
Piet

 

[Aladin Akyurek]

Need help trying to count unique values based on a value of "Yes" in another column?

In column E I have a list of cities, used the following formula to get a count of unique cities -

SUM(IF(FREQUENCY(MATCH(E$8:E$172,E$8:E$172,0),MATCH(E$8:E$172,E$8:E$172,0))>0,1))

But now I also want to only count the unique city if Column G has a value of "Yes".

Please help?

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(E$8:E$172<>"",IF(G$8:G$172="Yes", 
    MATCH("~"&E$8:E$172,E$8:E$172&"",0))),
      ROW(E$8:E$172)-ROW(E$8)+1),1))

 

[Robert Mika]

Another way:
=COUNT(1/FREQUENCY(IF($G$8:$G$172="YES",(MATCH($E$8:$E$172,$E$8:$E$172,0))),ROW($E$8:$E$172)-ROW(E8)+1))

array formula confirm with CTRL+SHIFT+ENTER

 

[clagar1610]

Thanks guys, both work!! Woop woop!!

 

[snowsurfwake]

Another way:
=COUNT(1/FREQUENCY(IF($G$8:$G$172="YES",(MATCH($E$8:$E$172,$E$8:$E$172,0))),ROW($E$8:$E$172)-ROW(E8)+1))

array formula confirm with CTRL+SHIFT+ENTER

I swear I'm putting in the same equation and doing CTRL+SHIFT+ENTER to get curly braces and it's not working . . . I'm at work so I'm using Excel 2003 and I'm not sure if something needs to be tweaked . . .