Criteria based ranking

Originally Posted by Bo_Ry

How about

=IF(ISNA(MATCH(E4,{"XXX";"YYY"},)),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)*(ISNA(MATCH(E$4:E$23,{"XXX";"YYY"},))),B$4:B$23),B$4:B$23)>0))+2,""),1)

Wow.. thats awesome it worked great. I am trying different scenarios to test your formula.

I modified your formula to rank 1 "XXX" & "YYY" and rest is to be ranked based on decreasing Price (in continuous).

=IF(ISNA(MATCH(E4,{"XXX";"YYY"},)),IF(SUM(N(FREQUENCY(IF((B$4:B$23>B4)*(ISNA(MATCH(E$4:E$23,{"XXX";"YYY"},))),B$4:B$23),B$4:B$23)>0))+2,""),1)

However i am not getting it. What is wrong with my edit of the formula??