Use RANK IF to rank with criteria
Here we have a dataset that lists the sales from different months.
Can we quickly rank these sales? The Excel RANK function will do that in a blink 😎
= RANK (C2:C9)
That was fast! But now what if I want to rank the sales for each month? The highest to lowest sales for January and February, both.
Now that’s what we call conditional ranking. It becomes conditional as we want the rank of the sales but only within a specific month 💪
For conditional ranking, we need the RANKIFS function. And as Excel has no such function, here’s a workaround for it.
- Write the COUNTIFS function as follows:
= COUNTIFS (
- As the first criteria range, define the range for the first criteria.
Our first criterion is the month of Sales, so we are referring to the column of months 📅
= COUNTIFS (A2:A9
- As the first criterion, refer to the month whose sales we want to be ranked.
As we need the sales for each month, so we will refer to the respective cell containing the month as the criteria.
= COUNTIFS (A2:A9, A2
- As the second criteria range, define the range where the second criterion is to be checked.
Our second criterion will help us rank the sales from highest to smallest so we are referring to the column that contains sales 📝
= COUNTIFS (A2:A9, A2, C2:C9
- Define the second criterion. The second criterion will simply be “greater than the corresponding sales”.
= COUNTIFS (A2:A9, A2, C2:C9, “>”&C2)