# Rank If in Excel & Google Sheets

This tutorial will demonstrate how to calculate “rank if”, ranking items in a list based­­ on criteria. ## 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: ## 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. ## 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. 