Rank sales reps within their region
IntermediateA global rank tells you who is the top performer overall, but it does not tell you who leads each region. A regional rank shows each rep's position among colleagues in the same territory, which is more useful for territory-specific incentives and team comparisons.
To rank within a group, you can use COUNTIFS to count how many same-region reps have higher sales, then add 1 to get the rank.
The pattern looks like:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2) + 1
COUNTIFS counts rows that meet all conditions. To make it rank descending, set one condition to compare against the current row's sales using a greater-than check. Each condition uses a full-range reference for the criteria range and a relative reference (or concatenated comparison) for the criteria.
Your task
- The table in A1:D13 lists sales reps with their region and total sales.
- In cell D2, write a formula that returns the regional rank for the first rep (Aisha Patel).
- The rank should show 1 for the rep with the highest sales in their region, 2 for the second highest, and so on.
- Make sure your formula can be filled down to D13 so every rep gets the correct rank within their own region.
Key points
- You need two criteria pairs: one to match the region, and one to check for higher sales.
- The sales comparison uses a greater-than condition: join ">" to the current row's sales cell.
- Use absolute references for the criteria ranges so they stay fixed when you copy the formula down.
When you are done, each rep should show a number from 1 to 4, with 1 being the best in their region.
Need some help?
Hint 1
Think of regional rank as the number of reps in the same region who have higher sales, plus 1 for the rep themselves.
Hint 2
The sales criterion needs to compare each row against the current row's sales value. Build a greater-than criterion by joining '>' to the current sales cell using ">"&C2.
Rank sales reps within their region
IntermediateA global rank tells you who is the top performer overall, but it does not tell you who leads each region. A regional rank shows each rep's position among colleagues in the same territory, which is more useful for territory-specific incentives and team comparisons.
To rank within a group, you can use COUNTIFS to count how many same-region reps have higher sales, then add 1 to get the rank.
The pattern looks like:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2) + 1
COUNTIFS counts rows that meet all conditions. To make it rank descending, set one condition to compare against the current row's sales using a greater-than check. Each condition uses a full-range reference for the criteria range and a relative reference (or concatenated comparison) for the criteria.
Your task
- The table in A1:D13 lists sales reps with their region and total sales.
- In cell D2, write a formula that returns the regional rank for the first rep (Aisha Patel).
- The rank should show 1 for the rep with the highest sales in their region, 2 for the second highest, and so on.
- Make sure your formula can be filled down to D13 so every rep gets the correct rank within their own region.
Key points
- You need two criteria pairs: one to match the region, and one to check for higher sales.
- The sales comparison uses a greater-than condition: join ">" to the current row's sales cell.
- Use absolute references for the criteria ranges so they stay fixed when you copy the formula down.
When you are done, each rep should show a number from 1 to 4, with 1 being the best in their region.
Need some help?
Hint 1
Think of regional rank as the number of reps in the same region who have higher sales, plus 1 for the rep themselves.
Hint 2
The sales criterion needs to compare each row against the current row's sales value. Build a greater-than criterion by joining '>' to the current sales cell using ">"&C2.