Criteria based ranking

Try this. It is working.
Normal formula in F4 and copied down.

=IF(OR($E4="XXX",$E4="YYY"),1,IF((C4="Urgent")*(D4>=I$2),SUMPRODUCT((C$4:C$23="Urgent")*(D$4:D$23>=$I$2)*(E$4:E$23<>"XXX")*(E$4:E$23<>"YYY")*(B$4:B$23>B4)*(B$4:B$23>0))+1+(SUM(COUNTIF($E$4:$E$23,{"XXX","YYY"}))>0),""))