Excel has a 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.
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. If we just use Excel’s RANK function, it would compare each day to all the other days in the list.
In the screen shot below, you can se 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 highest sales, so they should be tied at rank of 1.
- In week 2, Jan 10th has the highest sales, so it should have a rank of 1 for that week.
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:
The second criterion find items with a larger amount in the Sales column.
Then, 1 is added to that number, to get the ranking.
Check the Ranking
To check the ranking in week 1, look at the sales for Jan 3rd — 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
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.