Criteria based ranking

Originally Posted by Bo_Ry

Please try at F4

=IF(E4="",IF((C4="Urgent")*(D4>=I$2),SUM(N(FREQUENCY(IF((C$4:C$23="Urgent")*(D$4:D$23>=$I$2)*(B$4:B$23>B4),B$4:B$23),B$4:B$23)>0))+2,""),1)

i also thught of using IF for E4, but the issue with the formula is that though its giving rank 1 as per the Col. E entry but the ranks that is being computed in Col. F are not in series.
Check the sheet that i attached.
in col. F, Rank 2 is missing.