Excel RANK IF Formula Example

Excel RANK IF Formula Example

There is a Microsoft Excel RANK function, so you can calculate where each number stands in a list of numbers. There isn’t a RANKIF function though, if you need to rank based on criteria. Someone asked me for help with ranking daily sales, so I used COUNTIFS to make an Excel Rank If formula workaround.

Rank Daily Sales

The goal of the Rank If formula in this example is to rank each day’s sales, compared to other days in the same week. Which days have the highest sales, and which days have the smallest value for sales?

If we just use a RANK formula, the result from that worksheet function would compare each day to all the other days in the entire list.

In the screen shot below, you can see the first few rows of the sample data. The sales records for two weeks are visible.

  • In week 1, Jan 2nd and Jan 4th have the largest number of sales, so those duplicate values should be tied at rank of 1.
  • In week 2, Jan 10th sales (cell C8) has the highest value, so it should have a rank of 1 for that week.

ready for Excel Rank If formula

Get Ready to Rank the Sales

To manually rank the items largest to smallest for each week, we could count how many items are larger than each item.

  • In week 1, there are zero items with amounts larger than the Jan 2nd sales.
  • The rank for Jan 2nd is 1.
  • Using that information, we have a way to calculate the rank — the number of items larger, plus 1

Use COUNTIFS to Calculate RANK IF

The COUNTIFS function lets us count based on multiple criteria, so we’ll use that to create an Excel Rank IF formula.

For our criteria, we need to count:

  • other sales with the same week number
  • sales larger than the current row

Then, after COUNTIFS gives us that count, we’ll add 1 to get the rank.

To see more COUNTIFS examples, go to the the Count Functions page on my Contextures website.

The Rank IF Formula

Here’s the Rank If formula that I used for this example:

  • =COUNTIFS([Wk], [@Wk], [Sales], “>”&[@Sales])+1

The first criterion in the formula checks for other sales with the same week number:

  • =COUNTIFS([Wk], [@Wk]

The second criterion find items with a larger amount in the Sales column.

  • [Sales],”>”&[@Sales])

Finally, 1 is added to that number, at the end of the formula, to get the ranking.

  • +1

Check the Ranking

To check the ranking in week 1, look at the sales number for Jan 3rd, in cell C3 — 237.

  • There are 2 dates with a larger sales in week 1 — Jan 2nd and Jan 4th
  • Add 1 to that number, and Jan 3rd has a rank of 3, shown in cell D3

weekrank03

Download the Sample File

To download the sample file, go to the and RANK Function page on my Contextures website. The file is in xlsx format, and does not contain macros.

It contains other RANK examples too. You’ll see examples on how to break ties for duplicate numbers too, in case you need unique ranks.

_____________________

Rank If formula

_______________

2 thoughts on “Excel RANK IF Formula Example”

  1. This is so useful! And really easy to adapt to different situations.
    I used this to create uneatable “Top 5” lists in a sales report.
    I have a huge amount of products that are categorised under 5 segments, so instead of using the week lookup, I set my formula up to look at segment and applied the ranking to each segment.
    THANKS!

  2. Hi, i would like the above but if i have, for example, the same score in week 1 i would like the duplicate score in the same week to be the same rank number:
    wk score rank
    1 10 1
    1 10 1
    1 11 2
    1 12 3

    Is this possible?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.