You can use the following methods to create a RANK IF formula in Excel:
Method 1: RANK IF
=COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,">"&B2)+1
This formula finds the rank of the value in cell B2 among all values in the range B2:B11 where the corresponding value in the range A2:A11 is equal to the value in cell A2.
Using this method, the largest value is assigned a value of 1.
Method 2: Reverse RANK IF
=COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,"B2)+1
This formula finds the rank of the value in cell B2 among all values in the range B2:B11 where the corresponding value in the range A2:A11 is equal to the value in cell A2.
Using this method, the smallest value is assigned a value of 1.
The following examples show how to use each method with the following dataset in Excel:
Example 1: RANK IF
We can type the following formula into cell C2 to calculate the rank of the points value in cell B2 among all players on the “Mavs” team:
=COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,">"&B2)+1
We can then drag this formula down to each remaining cell in column C:
Here’s how to interpret the output:
- The first player scored the 3rd most points among all players on the Mavs team.
- The second player scored the 5th most points among all players on the Mavs team.
And so on.
Using this formula, the player with the highest points value on each team receives a rank of 1.
Example 2: Reverse RANK IF
We can type the following formula into cell C2 to calculate the rank of the points value in cell B2 among all players on the “Mavs” team:
=COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,"B2)+1
We can then drag this formula down to each remaining cell in column C:
Here’s how to interpret the output:
- The first player scored the 3rd lowest points among all players on the Mavs team.
- The second player scored the 1st lowest points among all players on the Mavs team.
And so on.
Using this formula, the player with the lowest points value on each team receives a rank of 1.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate Percentile Rank in Excel
How to Rank Values by Group in Excel
How to Rank Items by Multiple Criteria in Excel