Table of Contents
A RANK IF formula in Google Sheets is used to assign a rank to each item in a list based on a specified criteria. It is created using the RANK and IF functions, with the IF function used to filter the list and the RANK function used to assign the rank to each item. The formula uses a comparison operator to compare the value of a cell to a given value, and then ranks the item in the list accordingly. This formula provides a quick and easy way to sort a list of items based on user-defined criteria.
You can use the following methods to create a RANK IF formula in Google Sheets:
Method 1: RANK IF with One Criteria
=RANK(C1, FILTER(A:C, A:A="string"))
This formula finds the rank of the value in cell C1 among all values in column C where the corresponding value in column A is equal to “string.”
Method 2: RANK IF with Multiple Criteria
=RANK(C1, FILTER(A:C, A:A="string1", B:B="string2"))
This formula finds the rank of the value in cell C1 among all values in column C where the corresponding value in column A is equal to “string1” and the value in column B is equal to “string2.”
The following examples show how to use each method with the following dataset in Google Sheets:
Example 1: RANK IF with One Criteria
We can use the following formula to calculate the rank of the points values for the rows where the team is equal to “Mavs”:
=RANK(C2, FILTER(A:C, A:A="Mavs"))
The following screenshot shows how to use this syntax in practice:
Note that a rank of 1 indicates the largest value.
So, here’s how to interpret the rank values:
- The Guard on the Mavs team with 21 points has the 3rd highest points value among all players on the Mavs.
- The Guard on the Mavs team with 14 points has the 5th highest points value among all players on the Mavs.
Note that a #N/A value was produced for each of the Spurs players since we specified in the formula that we only want to provide a ranking for players on the Mavs team.
Example 2: RANK IF with Multiple Criteria
We can use the following formula to calculate the rank of the points values for the rows where the team is equal to “Mavs” and the position is equal to “Forward”:
=RANK(C2, FILTER(A:C, A:A="Mavs", B:B="Forward"))
The following screenshot shows how to use this syntax in practice:
Here’s how to interpret the rank values:
- The Forward on the Mavs team with 19 points has the 3rd highest points value among all players on the Mavs who are Forwards.
- The Forward on the Mavs team with 32 points has the 1st highest points value among all players on the Mavs who are Forwards.
And so on.
Note that a #N/A value was produced for any player who didn’t meet both criteria in our FILTER function.
Note: You can find the complete documentation for the RANK function in Google Sheets .