Conditional Selection

[matthew.clegg]

I am trying to return text depending conditional on multiple criteria.
Again best explained by example:
Area ID New Rank Select
A 1 1 7 Yes
A 2 0 4
A 3 0 2 Yes
A 4 1 8 Yes
A 5 0 3 Yes
A 6 0 1 Yes
A 7 0 5
A 8 0 6
B 1 1 7 Yes
B 2 1 4 Yes
B 3 1 2 Yes
B 4 1 8 Yes
B 5 1 3 Yes
B 6 1 1 Yes
B 7 1 5 Yes
B 8 0 6
The Column "Select" is what I am trying to calculate.
The rules are this:
1) Select per country
2) Always Select If new = 1 (doesn't matter how many)
3) If news are less than 5, add others up to five, selecting highest
ranked first
Cheers
Matt

 

[matthew.clegg]

I've got reasonably far:
=IF(C2=1,"Yes",IF(5-SUMPRODUCT(--($A$2:$A$17=A2),--($C$2:$C
$17=1))>=D2,"Yes",""))

This solves the example above, however if an entry has New=1 and also
a Rank<=5 the solution breaks down. Using example above if the ranks
are swapped for references D5 and D7 then the above equation only
returns yes 4 times. And I need min 5.

An help either modifying my attempt or a whole new great idea?