Excel formula score and award a place - Rank based on multiple parameters

Question

Let me share the problem, where I am trying to decide the winner list comparing multiple parameters:

First of all, I need to compare the fault points. The less you have the better place you get. If the fault points are equal, then I need to compare the time. Comparing the time, the faster you performed the greater place you get (green column represents the right result).

I have used this formula:

=IF(AA16="";"";COUNTIF($Z$16:$Z$24;"<"&Z16)+1+SUMPRODUCT(--($Z$16:$Z$24=Z16);--($AA$16:$AA$24>AA16)))

However, I get a wrong comparison for the time parameter. My guess is that it is either a small issue I am having or the formula itself is completely wrong.

Thanks in advance.

Answer 1

Use this formula instead:

=RANK(Z16,Z$16:Z$24,1)+SUMPRODUCT((Z$16:Z$24=Z16)*(AA$16:AA$24<AA16))

See image for reference:

Answer 2

Looks like this might be helpful. They have an example related to breaking ties that I think will work for your scenario.

Excel Functions: Rank