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.

**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

Great lesson.

Very elaborate