How to rank with multiple criteria in Excel

    How to rank in Excel with multiple criteria: This post will guide you ranking by multiple criteria in Excel.

    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)

     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.