Creating a RANK IF formula in Google Sheets allows you to rank values based on specific conditions. You can do this by combining the RANK and FILTER functions.
Here's how to do it in Google Sheets:
First, ensure you have your dataset ready in Google Sheets. For our example, let's consider a dataset with students' names, their scores, and class sections.
Column A (Name): Lists student names (Alice, Bob, Carol, David, Eve)
Column B (Score): Lists their scores (88, 95, 78, 82, 90)
Column C (Section): Indicates their section ("A", "B", "A", "B", "A")
The data looks like this:
Decide on the criteria based on which you want to rank your data. For our example, we want to rank the students by their scores, but only within Section A.
To rank values based on a single condition, you can structure your formula like this:
=RANK(B2, FILTER(B:B, C:C="string"))
For multiple conditions, you can write your formula like this:
=RANK(B2, FILTER(B:B, C:C="string1", D:D="string2"))
Following our example, we'll calculate the rank for each student in Section A based on their score. In cell D2, we enter the following formula to rank Alice's score among those in Section A:
=RANK(B2, FILTER(B$2:B$6, C$2:C$6 = "A"))
Press Enter to apply the formula.
You can drag the fill handle (the small blue dot at the bottom right corner of cell D2) down to fill the formula through D2 to D6.
This action applies the ranking formula to all students listed in Section A.
In our example, Alice, Carol, and Eve are ranked 2nd, 3rd, and 1st, respectively, among students in Section A based on their scores. Bob and David have an #N/A rank since they are not in Section A.
We hope that this article has helped you and given you a better understanding of how to use RANK IF Google Sheets. If you enjoyed this article, you might also like our articles on how to delete rows conditionally in Google Sheets and how to calculate tenure in Google Sheets.
