This guide will explain how to use a RANK IF formula in Google Sheets for conditional ranking.
Table of Contents
When analyzing data in your spreadsheet, you may want to rank values based on specific conditions. For example, given product sales data, you may want to rank products by their total sales while also limiting the output to products belonging to a specific category or supplier.
While Google Sheets does not have a built-in RANKIF
function, we can use custom formulas as a workaround to handle conditional ranking.
In this guide, we will provide a step-by-step tutorial on how to use custom formulas to perform a RANKIF
in Google Sheets for conditional ranking
The Anatomy of the RANK Function
The syntax of the RANK
function is as follows:
=RANK(value, data, [is_ascending])
Let’s look at each argument to understand how to use the RANK
function.
- RANK() refers to the function of the same name. This function returns the rank of a specified value in a given dataset.
- value argument refers to the value whose rank will be determined.
- data argument refers to the array or range containing the dataset to consider when ranking the value.
- is_ascending is an optional argument that determines whether to rank the values in data in ascending or descending order. By default, this value is set to 0.
- If is_ascending is set to 0, the greatest value will have a rank of 1. When set to 1, the least value in data will have a rank of 1.
- If data does not contain value in any cell or element, the RANK function returns an error.
The Anatomy of the FILTER Function
The syntax of the FILTER
function is as follows:
=FILTER(range, condition1, [condition2, ...])
Let’s look at each argument to understand how to use the FILTER
function.
- range refers to the range of values you want to filter.
- condition1 must be a column or row containing TRUE or FALSE values corresponding to a selected column or row of the range. It can also be an array formula that evaluates to TRUE or FALSE values,
- condition2, … are optional additional conditions to consider in your filter. Rows or columns in your range must pass all conditions to appear in the output.
A Real Example of Using a Conditional Ranking Formula in Google Sheets
Let’s explore a few simple examples where we can use the FILTER
and RANK
functions together to perform a conditional ranking in Google Sheets.
Ranking With a Single Criteria
Suppose we have a Google Sheets dataset containing product sales data. In this table, we have the following columns: Product, Category, Sales, and Ranking in Category.

We want to fill out the fourth column with the ranking of that product in terms of sales for just the category it belongs to. For example, if product P0001 has a ranking of 1, this indicates that it is the top-performing product in category A.

In the sheet above, we were able to fill out the rankings of each product within their categories. For example, we can identify that product P0008 is the top-ranking product in Category B and P0015 is the top-ranking product in Category C.
To retrieve this conditional ranking, we can use the following formula:
=RANK(C2, FILTER($A$2:C, $B$2:B=B2))
Let’s try to understand what the formula above tries to accomplish.
The FILTER
function looks at the data in the range A2:C and filters for all data where the value in column B is equivalent to the value in the current row. This filtered array is then used as the basis for ranking the sales figure of the current row, which is accomplished using the RANK
function.
Ranking With Multiple Criteria
In some cases, you may also want to rank data conditionally using multiple criteria. Using our last example, we may also want to find a product’s ranking in terms of sales within both its category and branch.

To get a RANKIF
with multiple criteria, we can add more conditions to our FILTER
function:
=RANK(D2, FILTER($A$2:D, $B$2:B=B2, $C$2:C=C2))
Click on the link below to create your copy of our examples.
Head to the next section to read our step-by-step tutorial on how to set up a RANKIF
function in Google Sheets.
How to Use RANK IF in Google Sheets (Conditional Ranking)
- First, analyze the dataset you wish to rank. Identify which columns will be used for ranking and which columns will be used as criteria. Leave an empty column for the actual conditional ranking.
In the example above, we’ll be ranking products based on the number of total sales in column C, and we’ll also be using the category field (column B) as a criterion for ranking. - Next, we’ll write our custom
RANKIF
formula in cell D2 to calculate the rank of the first product in our dataset.
In our example, we used the following formula: =RANK(C2, FILTER($A$2:C, $B$2:B=B2))
Note that you’ll need to adjust this formula’s cell references depending on how your dataset is structured. - Hit the Enter key to evaluate the formula.
The formula states that product P0001 ranks sixth in terms of sales for the category it belongs to. - Use the Fill Handle to copy the formula down the rest of the column.
You can quickly perform this action by selecting cell D2 and double-clicking the solid circle on the lower-right corner. - We can add multiple criteria to our
RANKIF
formula by adding more arguments to ourFILTER
function.
In our example above, we used the formula =RANK(D2, FILTER($A$2:D, $B$2:B=B2, $C$2:C=C2)).
We simply modify ourFILTER
function to only compare values where the category and branch match the current row.
To learn more about ranking values in Google Sheets, you can read our post on how to rank text uniquely in Google Sheets.
That’s all for this guide! Don’t forget to check out our library of spreadsheet resources, tips, and tricks for both Google Sheets and Microsoft Excel!
