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