Return to Excel Formulas List

Rank If in Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to calculate “rank if”, ranking items in a list based­­ on criteria.

rank if Main Function

RANK IF Formula

With no native RANKIF Function in Excel, we can use the COUNTIFS Function to rank items based on single or multiple criteria. Let’s walk through an example.

To rank the salespersons by revenue for each region separately, we use the COUNTIFS Function to conditionally restrict ranking like so:

RANK IF (Reverse)

How does the formula work?

The COUNTIFS Function is used to count cells with dates, numbers, and text that meet specific criteria. To calculate “rank if”, we will use two criteria:

  1. Count only the values that are greater than the current row’s value. (This is where the “ranking” occurs).
  2. Any condition (or conditions) that you want to specify.Note: the “+1” at the end of the formula is required to start the ranking at 1 instead of 0.

Reverse RANK IF Formula

In the previous examples, we ranked Salespersons by revenue from largest to smallest.

To rank the salespersons by revenue in reverse order, simply switch the sign in the first criteria to be less than “<” instead.

RANK IF

Locking Cell References

 

To make our formulas easier to read, we’ve shown the formulas without locked cell references:

But these formulas will not work properly when copy and pasted elsewhere with your Excel file.

Read our article on Locking Cell References to learn more.

Rank If in Google Sheets

All the examples explained above work the same in Google sheets as they do in Excel.

rank if Google Function