Excel RANK IF Formula Example

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.

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])

Then, 1 is added to that number, to get the ranking.

+1

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

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.

_____________________

 

Rank If formula

_______________

Save

Save

Save

You may also like...

1 Response

  1. David N says:

    In my experience, COUNTIFS can be significantly slower than FREQUENCY when dealing with large data sets. Additionally, COUNTIFS can only operate on literal ranges as opposed to arrays, if such an ability were ever needed. As such I might use something like the following, entered as array functions.

    Rank lowest to highest (the opposite of your results)
    {=FREQUENCY(IFERROR([Sales]/([Wk]=[@Wk]),””),[@Sales])}

    Rank highest to lowest (matching the results of your example)
    {=INDEX(FREQUENCY(IFERROR([Sales]/([Wk]=[@Wk]),””),[@Sales]),2)+1}

    Binary division handles the conditional logic, and IFERROR ensures that those failing the condition and resulting in #DIV/0! are converted to text (the empty string) so that they’re ignored by FREQUENCY. Then FREQUENCY does its normal job of counting all numbers less than or equal to the given bin value. Numbers greater than the bin value are counted in the “other” group, which is the second value returned by FREQUENCY in this case. So INDEX is used to isolate that second result, and 1 is added for the same reason as in your solution.

Leave a Reply

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