For example, as shown below, there are two groups (A and B) with 5 items in each group. Each item has a score in column C. Now there are two rankings done with this data set. Column D has the overall rankings and Column E has the group-wise rankings. Now there are two rankings done with this data set. Column D has the overall rankings and Column E has the group-wise rankings.
While the overall ranking can easily be done using the RANK function, the one for groups is a bit more complex.
Rank within Groups in Excel
Here is the formula that will give the ranks within groups in Excel: =SUMPRODUCT((A2=$A$2:$A$11)*(C2<$C$2:$C$11))+1 How does this work? This formula checks for 2 conditions:
(A2=$A$2:$A$11) – This part returns an array of TRUEs/FALSEs based on the group. So if you use this in cell E2, it will check A2:A11 and return TRUE wherever it finds Group A and FALSE when it finds any other group. (C2<$C$2:$C$11) – This checks whether the score is less than the other scores in C2:C11, and returns TRUE if it’s less and FALSE if it’s more.
When these two arrays of TRUEs/FALSEs are multiplied, it would return TRUE only in those cases where both the conditions are met, i.e., the group matches and the scores are less than the score in the row where the formula is used. The SUMPRODUCT formula then simply returns the total count of such instances where the conditions are met. 1 is added to the SUMPRODUCT result to get the rank of the given score within that group. Click here to download the example file. I hope you found this tutorial useful!
Automatically Sort Data in Alphabetical Order using Formula. How to Generate Unique Random Numbers in Excel. How to Calculate Age in Excel using Formulas. How to Extract a Substring in Excel Using Formulas. How to Calculate PERCENTILE in Excel (Easy Formula + Examples)
By the way, above you say the formula ends with “+1”, but in the example file, it is not there.
