In this article:

RANK IF in Google Sheets: How to Use It in 2024

May 8, 2024

How to Use RANK IF in Google Sheets

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:

Step 1: Set Up Your Data

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:

rankif google sheets

Step 2: Determine Your Criteria

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.

rank if google sheets

Step 3: Use the RANK and FILTER Functions Together

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. 

rank if in google sheets

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.

use rankif in google sheets

This action applies the ranking formula to all students listed in Section A. 

rankif formula in google sheets

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->