Composite Ranks + Eligibility

[RO24]

Morning - I've asked a question on this topic prior to this (https://www.excelforum.com/excel-for...k-formula.html), but I have a further question.

The below example is an issue I'm running into currently, as my formula looks to only be looking at the "1", as in 1st place. The Yes/No are the reps that are eligible for the contest, based on criteria in 3 separate columns. The $4,500 is a bonus that is eligible depending on if Col. A (Yes/No) hits the requirements. My question is, based on this formula that I've been using to calculate eligibility for bonus $'s (we'll call it Col.Q) - =IFERROR(VLOOKUP($B75,$AL$8:$AM$11,2,0)/COUNTIFS($B$74:$B$83,$B75,$A$74:$A$83,"Yes")," ")

What can I add to the formula to look at the "yes/No", before looking at the Ranks in Col. B?


Yes 1 2.7 2 5 1 $4,500 KD
No 1 2.7 1 2 5 $4,500 JG
No 3 3.7 8 1 2 KC
No 4 5.3 3 4 9 JC
No 5 6.0 7 7 4 DK

Thank you

[RO24]

Sorry - I found this elsewhere....apologies, if this can removed, please remove the post. Thanks.