Contest Results based on 4 Criteria

I used a helper column to determine ranking.
In the helper column (I used H) is this formula copied down.
=MIN(C5,1)+D5/100+F5/100000000
That formula levels off all who scored over 100% in column C and then adds in the qualifiers for other columns. Then using a simple RANK formula in I gives the ranks
=RANK(H5, $H$5:$H$9)
See attachment. Is this what you are looking for? You can hide Column H if you need to.

Is your explanation complete.? I got as far as No 2 and then got confused. A clearer explanation AND a manual calculation of your EXPECTED result just might help...

No expected results on your attached sheet...

Did you upload the incorrect file? I don't see expected results.

If I'm reading this right,
1. We are only looking at names that made or exceeded 100%. (What if only 2 names exceeded 100, is there no 3rd place? Is 3rd place then decided by highest performance (i.e 98%)?)
2. First place is then determined by highest YoY Growth (from among all who exceeded 100% Performance)
3. Second place is then determined by highest Units YoY (excluding first place winner of course) and totally ignoring YoY Growth.
4. Third place is determined by ?

If I'm understanding the progression properly, then try the formula below in H5. It should be array-entered with Ctrl + Shift + Enter instead of Enter:

=IF($D5=MAX(($C$5:$C$9>=1)*($D$5:$D$9)),1,IF($E5=MAX(($C$5:$C$9>=1)*($E$5:$E$9)*(($D$5:$D$9)<>MAX(($C$5:$C$9>=1)*($D$5:$D$9)))),2,IF($C5>=1,3+COUNTIFS($F$5:$F$9,">"&$F5,$C$5:$C$9,">=1",$D$5:$D$9,"<>"&MAX(($C$5:$C$9>=1)*($D$5:$D$9)),$E$5:$E$9,"<>"&MAX(($C$5:$C$9>=1)*($E$5:$E$9)*(($D$5:$D$9)<>MAX(($C$5:$C$9>=1)*($D$5:$D$9))))),"NQ")))

Fill down through H9. For a bigger range, obviously you'll need to change the "$9"s in the formula to whatever your true bottom entry is. I've been experimenting with changes in your data and the formula appears to hold up, but test it out to see if it works for you.

The version in post #11 does not account for blanks in F. It would rank that entry as 3rd, if it qualifies, which probably isn't what you want. You could reorder things and use this (array-entered) in H5:

=IF($D5=MAX(($C$5:$C$9>=1)*($D$5:$D$9)),1,IF($E5=MAX(($C$5:$C$9>=1)*($E$5:$E$9)*(($D$5:$D$9)<>MAX(($C$5:$C$9>=1)*($D$5:$D$9)))),2,IF($C5<1,"NQ",IF($F5="","-",3+COUNTIFS($F$5:$F$9,">"&$F5,$C$5:$C$9,">=1",$D$5:$D$9,"<>"&MAX(($C$5:$C$9>=1)*($D$5:$D$9)),$E$5:$E$9,"<>"&MAX(($C$5:$C$9>=1)*($E$5:$E$9)*(($D$5:$D$9)<>MAX(($C$5:$C$9>=1)*($D$5:$D$9)))))))))

...and a blank in F would return a "-", which might be better.

As to the ranking - it's not ranking the first two. It explicitly identifies the first place entry, according to your "first place" criteria, then it explicitly identifies the second place entry according to the "second place" criteria. Once it checks for NQs and blanks in F (in the version above), it then ranks the remaining values. Superficially, it doesn't look like a RANK formula, but:

3+COUNTIFS($F$5:$F$9,">"&$F5,$C$5:$C$9,">=1",$D$5:$D$9,"<>"&MAX(($C$5:$C$9>=1)*($D$5:$D$9)),$E$5:$E$9,"<>"&MAX(($C$5:$C$9>=1)*($E$5:$E$9)*(($D$5:$D$9)<>MAX(($C$5:$C$9>=1)*($D$5:$D$9)))))

...is actually a roundabout method for conditional ranking. It is counting the number of entries that are greater than the current row's F value ($F$5:$F$9,">"&$F5), over 100% in C ($C$5:$C$9,">=1"), not the #1 entry ($D$5:$D$9,"<>"&MAX(($C$5:$C$9>=1)*($D$5:$D$9)),$E$5:$E$9), and not the #2 entry ($E$5:$E$9,"<>"&MAX(($C$5:$C$9>=1)*($E$5:$E$9)*(($D$5:$D$9)<>MAX(($C$5:$C$9>=1)*($D$5:$D$9)))))).
It adds 3 so that the greatest of those values (the row with zero F numbers higher, as determined by the first term, so COUNTIFS returns 0) shows a 3. The row with one number higher in the COUNTIFS is 4, etc. It's not quiet as elegant as a standard RANK, but until Excel gives us RANKIFS, a little creativity is necessary.

Hopefully this helps? Let me know if you have any additional questions.

I'm glad I could assist. Thanks to you and ChemistB for the rep!