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?