You can use the following formulas to rank values by group in Excel:
Formula 1: Rank Values by Group
=SUMPRODUCT(($A$2:$A$13=A2)*($B$2:$B$13>B2))+1
This particular formula finds the rank of the value in cell B2 that belongs to the group in cell A2.
This formula assigns a rank of 1 to the largest value, 2 to the second largest value, etc.
Formula 2: Rank Values by Group (Reverse Order)
=SUMPRODUCT(($A$2:$A$13=A2)*($B$2:$B$13B2))+1
This particular formula finds the reverse rank of the value in cell B2 that belongs to the group in cell A2.
This formula assigns a rank of 1 to the smallest value, 2 to the second smallest value, etc.
The following examples show how to use each formula in Excel.
Example 1: Rank Values by Group in Excel
Suppose we have the following dataset in Excel that shows the points scored by basketball players on various teams:
We can use the following formula to rank the points by team:
=SUMPRODUCT(($A$2:$A$13=A2)*($B$2:$B$13>B2))+1
We’ll type this formula into cell C2, then copy and paste the formula down to every remaining cell in column C:
Here’s how to interpret the values in column C:
- The player with 22 points for the Mavs has the 2nd largest points value among Mavericks players.
- The player with 28 points for the Mavs has the 1st largest points value among Mavericks players.
- The player with 31 points for the Spurs has the 2nd largest points value among Spurs players.
And so on.
Example 2: Rank Values by Group (Reverse Order) in Excel
Once again suppose we have the following dataset in Excel that shows the points scored by basketball players on various teams:
We can use the following formula to rank the points in reverse order by team:
=SUMPRODUCT(($A$2:$A$13=A2)*($B$2:$B$13B2))+1
We’ll type this formula into cell C2, then copy and paste the formula down to every remaining cell in column C:
Here’s how to interpret the values in column C:
- The player with 22 points for the Mavs has the 3rd smallest points value among Mavericks players.
- The player with 28 points for the Mavs has the 4th smallest points value among Mavericks players.
- The player with 31 points for the Spurs has the 4th smallest points value among Spurs players.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Rank Items by Multiple Criteria in Excel
How to Sort by Multiple Columns in Excel