You can use the following syntax to rank items by multiple columns in Google Sheets:
=RANK(B2,$B$2:$B$11)+SUMPRODUCT(--($B$2:$B$11=$B2),--(C2$C$2:$C$11))
This particular formula ranks items in descending order first by the values in column B, then by the values in column C.
The following example shows how to use this formula in practice.
Example: Rank Items by Multiple Columns in Google Sheets
Suppose we have the following dataset in Google Sheets that shows the points and assists for 10 different basketball players:
Now suppose we’d like to rank these players from best to worst, assigning a value of “1” to the best player and “10” to the worst player.
We’ll rank the players from best to worst based on the value in the Points column. If there are ties, we’ll then refer to the value in the Assists column.
We’ll use the following formula to rank the players:
=RANK(B2,$B$2:$B$11)+SUMPRODUCT(--($B$2:$B$11=$B2),--(C2$C$2:$C$11))
The following screenshot shows how to use this formula in practice:
The player with a ranking of 1 is considered the best and the player with a ranking of 10 is considered the worst.
Here’s how our formula worked:
- Rank the players from best to worst based on the value in the Points column.
- If there are ties, refer to the value in the Assists column.
For example, players A and B tied for having the most points. However, player A had more assists so they received a ranking of 1 and player B received a ranking of 2.
Note that any players who had identical values for Points and Assists (like players E and F) received the same ranking.
If you’d like to rank the players in ascending order (1=worst, 10 =best) then we can use the following formula:
=RANK(B2,$B$2:$B$11, 1)+SUMPRODUCT(--($B$2:$B$11=$B2),--(C2$C$2:$C$11))+1
The following screenshot shows how to use this formula in practice:
The “best” player now has a ranking of 10 and the “worst” player has a ranking of 1.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets:
Google Sheets: How to Filter for Cells that Contain Text
Google Sheets: How to Use SUMIF with Multiple Columns
Google Sheets: How to Sum Across Multiple Sheets