How to Use a RANK IF Formula in Excel

To use a RANK IF formula in Excel, you'll need to combine the RANK and IF functions, as Excel does not have a built-in RANK IF function. This combination can be used to rank values based on certain criteria or conditions.

Here's a step-by-step guide on how to use a RANK IF formula in Excel:

1. First, make sure your data is organized and you have a column where you want to display the ranking.
2. Click on the cell where you want to display the first rank value.
3. Enter the following formula, adjusting the cell ranges, criteria, and values to match your data:

=IF(criteria_range=criteria, RANK(value, value_range, [order]), "")

• criteria_range: The range of cells that you want to evaluate based on the criteria.
• criteria: The condition that needs to be met for the ranking to occur.
• value: The cell reference for the value you want to rank.
• value_range: The range of cells containing the values you want to rank.
• [order]: [Optional] A number that determines the ranking order. Use 0 or omitted for descending order (default) and 1 for ascending order.

4. Press Enter to see the ranking for the first value that meets the specified criteria.
5. Copy the formula down the column to apply it to the rest of the data.

Example

Let's say you have a list of sales representatives and their sales numbers. You want to rank them based on their sales, but only if they achieved a minimum of $1000 in sales.

1. Organize your data with a column for sales representatives, a column for sales numbers, and a column for the ranking.

A               B         C
------------------------------
1  | Sales Rep   | Sales  | Rank
2  | Alice       | 1200   | 
3  | Bob         | 900    | 
4  | Charlie     | 1300   | 
5  | Dave        | 800    | 

2. Click on cell C2, where you want to display the first rank value.
3. Enter the following formula in C2:

=IF(B2>=1000, RANK(B2, $B$2:$B$5, 0), "")

4. Press Enter to see the ranking for Alice, who has sales of $1200.
5. Copy the formula down the column C to apply it to the rest of the sales representatives.

Your final data should look like this:

A               B         C
------------------------------
1  | Sales Rep   | Sales  | Rank
2  | Alice       | 1200   | 2
3  | Bob         | 900    | 
4  | Charlie     | 1300   | 1
5  | Dave        | 800    | 

In this example, the RANK IF formula only ranks Alice and Charlie, as they met the criteria of having sales of at least $1000.