Ranking by multiple criteria columns

I 'm trying to rank my students by 2 criteria. First from RESULT and then POINT.

In the example, the resulting order should be, NUNU, NENE, NONO, NANA and NINI.

It cannot be done by adding up RESULT and POINT. I searched online and cannot really understand how to do the ranking.

3 Answers

I would like to suggest you a solution with different approach:

Write this formula in Cell J2(Rank Col) & fill it down, finally Sort data in Ascending order by Rank Column:

=COUNTIF($G$2:$G$6,">"&G2)+1+SUMPRODUCT(--($G$2:$G$6=G2),--($H$2:$H$6>H2))

The solution is quite simple:

Enter the following formula in I2 and ctrl-enter/copy-paste/fill-down the column:

=G2*10^3+H2

Note that this will only work correctly if the maximum allowed value in the POINT column is 999.

For larger values, the 10^3 in the formula needs to changed. For example, if the maximum allowable value is 9999 then 10^4 is required.

If you want a more robust formula that will work no matter what the values in column H, use this:

=G2*10^CEILING(LOG10(MAX($H$2:$H$6)),1)+H2

If you want the "actual" rank, unfortunately there's no way to use the above formulas with the RANK() function, as it only allows references for the second argument, not arrays. A completely different formula is required:

Enter the following formula in I2 and ctrl-enter/copy-paste/fill-down the column:

=1+SUMPRODUCT(($G$2:$G$6>G2)+($G$2:$G$6=G2)*($H$2:$H$6>H2))

An equivalent array entered (Ctrl+Shift+Enter) formula is:

{=SUM(1,--($G$2:$G$6>G2),($G$2:$G$6=G2)*($H$2:$H$6>H2))}

Of course, if your requirement is to sort the table, the simplest solution is to do a multi-column sort:

So, looking at your previous question as well as this one, it become obvious that you wish to rank students based on the number of A's, then by the number of B's, etc, and to tiebreak those ranks based on the sums of the marks for each subject.

With that in mind, here is the no helper column formula to do just that:

Array enter (Ctrl+Shift+Enter) the following formula in M5 and copy-paste/fill-down into the rest of the column (don't forget to remove the { and the }):

{=
SUM( 1, --( MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))) >INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9)) ), ( MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))) =INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9)) ) *( MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))) >INDEX(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9)) )
)}

The following is the equivalent minified version of the above formula. I strongly advise not to use it, but to use the prettified formula version instead. Doing so will make the formula much, much easier to maintain.

{=SUM(1,--(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))>INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))),(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))=INDEX(MMULT(($B$5:$L$9="A")*6^2+($B$5:$L$9="B")*6^1+($B$5:$L$9="C")*6^0,--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9)))*(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9)))))>INDEX(MMULT(IFERROR(--$B$5:$L$9,0),--(0<ROW(INDEX(M:M,1):INDEX(M:M,COLUMNS($B$5:$L$9))))),1+ROW()-ROW($B$5:$L$9))))}

Explanation:

Looking at the structure of the prettified formula, it becomes clear that it is essentially the same as the "proper" rank array-entered formula from my previous answer

{=SUM(1,--($G$2:$G$6>G2),($G$2:$G$6=G2)*($H$2:$H$6>H2))}

with the RESULT and POINT helper columns being replaced with a MMULT(…) function, and the single cell reference into those columns replaced by INDEX(MMULT(…),1+ROW()-ROW($B$5:$L$9))

If you wish to understand how the MMULT() function is used here, you could start by checking out a simpler usage in my answer to another question.

It should be fairly obvious how to adjust the formula for more grades. For example, to add a D, append a +($B$5:$L$9="D")*6^0 to the end of the others, and increment the other powers.

As mentioned in the answer to the previous question, if the table is expanded with more subjects, the 6 has to be increased so it is at least one more than the new number of subjects in the table.