Excel ranking formula based on criteria

[kypok999]

Hi guys,

Broke my head on this one so I have criteria in A2


As in picture I want A2 to be ranked by AI first starting ranking 1 as highest but if there are duplicate rankings like column AI6 I want it to be ranked first by AI and then by AB combined and display highest ranking so for example AI2 is 1 and AB2 is 3 then it means AJ should be 1 as AI is higher then AB2

https://imgur.com/a/SdlF8

 

[stunnrock]

Something like this?

=IF(COUNTIF($AJ$2:$AJ$10,$AJ2)=1,$AJ2,MAX($AJ2,RANK($AB2,$AB$2:$AB$10)))