# How to Rank Values by Group in Excel

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\$13<B2))+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\$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 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.