# How to calculate rank with a condition in Excel? RANK IF

Posted on

In this blog post, we will learn how to calculate Rank with a condition. As Excel does not have a RANKIF function yet, we will use a different method to arrive at the solution.

## VIDEO DEMO

If you would like to be notified of these videos, please subscribe to our YouTube channel.

## DATA

Let’s start with the input data. This is for a list of 10 employees, where the Department Name and the employee’s score are provided.

Let’s assume that the score is out of max of 10, higher the number, the better it is. We need to find the rank of each employee within the Department that the employee belongs to.

## HOW TO CALCULATE OVERALL RANK AND RANK IF

### Step 2: Calculate Overall Rank with RANK function

First, let’s calculate the overall rank (rank of an employee within the company).

We can use the RANK function in our formula.

General Syntax

= RANK( number, ref, [order])

In our context

= RANK(“Specific Employee’s Score”, “List of All Employees’ Scores”,0)

0 is to indicate that we need the rank based on descending order (highest is first rank)

Tip: If we need to find the rank based on lowest, use 1 instead of 0 in the function for the Order parameter.

That was straightforward, as we have a function RANK specifically designed for this use case.

### Step 3: Calculate Overall Rank without RANK function

If we need to determine rank within Department – for example, rank within Finance, or rank within HR, then it is not so straight forward, as we don’t have a RANKIF function. 😊

Not a problem. Excel has enough functions for us to find a solution to this.

If we think about what ranking does, first rank means that there is no other number greater than that. We can translate this to

If number of values greater than the current value = 0, then that is the highest number. In other words, Rank = 1
If number of values greater than the current value = 1, then that is the second highest number. In other words, Rank = 2

We can convert this to a formula as below.

=COUNTIFS([Score],”>”&[@Score])+1

We must add 1 at the end of the formula because for the first rank, there are 0 values greater.

As you can see above, the two formulas give us the same result.

### Step 4: Calculate Rank within Department

Now, we need to modify second formula to calculate rank within each department.

The COUNTIFS function allows us to provide multiple conditions (unlike the RANK function). So, we can add Department condition to the same formula.

=COUNTIFS([Score],”>”&[@Score],[Department],[@Department])+1

Andrea has the top score in Finance, while Cathy has the top score in HR.

We can quickly verify this by using the Sort options.

Sorting just by Score from Largest to Smallest,

Sorting by Department and Score from Largest to Smallest,