How to Use RANK IF in Google Sheets (Conditional Ranking)

This guide will explain how to use a RANK IF formula in Google Sheets for conditional ranking.

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)

  1. 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.
  2. 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.
  3. 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.
  4. Use the Fill Handle to copy the formula down the rest of the column.
    undefined
    You can quickly perform this action by selecting cell D2 and double-clicking the solid circle on the lower-right corner.

  5. We can add multiple criteria to our RANKIF formula by adding more arguments to our FILTER 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 our FILTER 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! 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like