Ranking in Excel with multiple criteria

Question

For example, I need to create a merit list of few student based on total marks (column C), then higher marks in math (column B) -

A        B  C   D
-------------------------
Student1 80 220 1
Student2 88 180 3
Student3 90 180 2

Expected merit position is given in column D.

I can use RANK function but I can only do that for one column (total number). If total number of multiple student is equal, I could not find any solution of this.

Answer 1

You can try this one in D1

=COUNTIF($C$1:$C$99,">"&C1)+1+SUMPRODUCT(--($C$1:$C$99=C1),--($B$1:$B$99>B1))

and then copy/fill down. let me know if this helps.

Explanation

Your first criteria sits in column C, and the second criteria sits in Column B. Basically, first it is counting the number of entries ($C$1:$C$99) that are bigger than the entry itself ($C1). For the first one in the ranking, you will get zero, therefore you need to add 1 to each result (+1). Until here, you will get duplicate rankings if you have the same value twice.
Therefore you need to add another argument to do some extra calculations based on the second criteria:
To resolve the tie situation, you need to sumproduct two array formulas and add the result to the previous argument, the goal is to find the number of entries that are equal to this entry with $C$1:$C$99=C1 and have a bigger value in the second criteria column $B$1:$B$99>B1:

you add -- to convert TRUE and FALSE to 0s and 1s so that you can multiply them:
SUMPRODUCT(--($C$1:$C$99=C1),--($B$1:$B$99>B1))
the first array is to see how many ties you have in the first criteria. And the second array is to find the number of bigger values than the entry itself.

Note you can add as many entries as you like to your columns, but remember to update the ranges in the formula, currently it is set to 99, you can extend it to as many rows as you want.