RANK SORTBY Results in Ascending Order

I came across a problem that I thought should be a simple formula, but it's not returning the desired result. And I can't seem to figure it out.

In column C (listed under P#) is the player's number of each contestant in a game.

In column D (listed under A) are the players' rating for that category.

In column E (listed under B) is another rating for the players in a separate category.

In column G is the result of P# being sorted by A and then by B in ascending order.

In columns J and K are the mock up results I'm seeking.

Because of how I've sorted the players with the SORTBY function in column G, player #2 is at the top. That's because of the players earned the highest rating (7) in Category A, #2 has the highest rating among them in Category B. So he gets the highest rank rating of 10, shown in column K.

A few things to note:

I want to sort and rank so that the highest is shown as a 10, not a 1. So I believe sorting in ascending is the way to accomplish that.

Also, please note: Players #1, #3 and #10 all received a 0 in Category A and in B, players #1 and #10 both received an 84, while player #3 has a lower score for B (80). Therefore, in my mock up results, I have players #1 and #10 tied for the 3 rank instead of ranking them 3 and 2. And player #3 getting the lowest rank score of 1. This is how the RANK function would rank them if the scores in A and B were added together and then ranked in ascending order.

But here is the problem:

I can't do it that way because notice how the sum of A and B for players #3 and #6 = 80. The sum is the same but they should not be ranked the same, because player #6 has a 2 in Cat. A and my SORTBY function in column G is sorting by A first, then by B.

More examples: Look at player #4, his total is 84, which is the same total that #1 and #10 have. But player #4 is ranked 5 due to him having a 2 for category A.

Also, look at Player #9, he has the highest sum of A and B and also has the highest score in Category B, but I have him ranked 8 (third highest) because in category A, two other players have a 7 in that category and he only earned a 5.

So basically what I'm trying to do is RANK the results of the SORTBY results but because of the issues I jest mentioned, I can't use the sum of A and B to rank them.

I'm hoping I've explained this well enough so that one of the experts here can see what I'm trying to do and write a formula that can rank the players like I have shown in columns J and K.

I appreciate any help you can offer. Thank you.

@ExceLogan, thank you for your reply. I like that your formula spills the results but I found it returns one error. It returns 2 for player #3. It should return a 1. Any idea why that is?

@Phuocam, thank you for your reply. I found both your formulas gives the exact same results as shown in column K. That's excellent. They both work equally well. May I know is one formula preferable to the other?


EDIT: @Phuocam, I just want to say that I find it rather surprising that the formulas you posted do not even use RANK, nor SORTBY functions. It's also surprising the players numbers under P# is not used. However both formulas work exactly as I want them to.

Just amazing to me how when it comes to writing formulas in Excel, that folks such as yourself are on a whole other level.


=SUMPRODUCT(--($D$4:$D$13*10000+$E$4:$E$13<=D4*10000+E4)) (This one really amazes me that you are multiplying a range by 10000)

I will try to dissect these formulas and learn from them.

- Thank you again.

Inspired by ExceLogan's solution:

Please try:

Formula:

Please Login or Register  to view this content.

You are Welcome! Thanks for the feedback and rep . Glad to have helped.