Rank one rep
What is Bo's rank by booked amount, highest first?
Formula: =RANK(D3,D2:D6) → 3
Hands-on Excel exercises
Excel Skills for Sales Operations
Practice Excel for sales operations: quota attainment by region with SUMIFS, RANK, MAXIFS, and a count of reps at quota, with worked answers.
Sales operations spreadsheets answer questions that managers ask every month: how is each region doing against quota, who is leading, who is below target? Employers hiring for the role look for conditional sums, ratios against a target, rankings, and the discipline to reconcile each dashboard figure to the underlying rows.
The live sheet lists five reps with a region, a quota, and the amount booked. Your task is the East region's attainment, booked divided by quota for the region as a percent, with SUMIFS in both the numerator and the denominator. Note why: attainment for a group is the ratio of totals, not the average of each rep's percentage. The worked answers then rank one rep, count the reps who reached quota, and find the largest booking in the West.
Check every figure against a quick filter of the same rows, and always state the period and the stage rules behind the booked amount. Use rep IDs rather than names as keys in real work, because names collide and change spelling. Rankings need a decision about ties, so write it down. For the campaign side of the funnel, the marketing page covers ratios such as click-through and return on spend, and the data analysts page covers joining reference tables.
Your turn
Your turn: in F2, calculate the East region's quota attainment: total booked divided by total quota for East, as a percent rounded to one decimal.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Quota | Booked | ||
| 2 | Ava | East | 100000 | 112000 | ||
| 3 | Bo | West | 90000 | 71000 | ||
| 4 | Cam | East | 80000 | 64000 | ||
| 5 | Dev | West | 120000 | 138000 | ||
| 6 | Eve | North | 70000 | 70000 |
Not yet solved.
Answer: =ROUND(SUMIFS(D2:D6,B2:B6,"East")/SUMIFS(C2:C6,B2:B6,"East")*100,1)
Practice under a time limit
Try the free Sample mock exam with 4 tasks in 8 minutes, or browse the level and interview mock exams included with Pro + Exams.
Related exercises
Open a task in its lesson.
- In D2:D5, tidy up the names: no extra spaces, and each word capitalized.
- In G2:G4, look up the item price.
- In F4, find the average deal amount in Denver.
- Build a second PivotTable: the average deal amount by city, with the result in F8.
- Build a line chart of revenue and expenses together (A1:C7).
- In G6, calculate the sales for March 2026.
- In F4, count the deals for the month, and in F5 calculate the average deal (revenue / deals).
Worked answers
Try the spreadsheet first, then compare your formula and result.
Count reps at or above quota
How many reps booked at least their quota?
Formula: =SUMPRODUCT((D2:D6>=C2:C6)*1) → 3
Largest booking in one region
What is the largest booked amount among West reps?
Formula: =MAXIFS(D2:D6,B2:B6,"West") → 138000
Frequently asked questions
Why is attainment a ratio of totals?
Averaging each rep's percentage ignores the size of the quotas and can mislead. Divide the group's booked total by its quota total.
How does RANK treat ties?
Tied values share the same rank and the next rank is skipped. Decide in advance how to present ties.
Why use IDs rather than names as keys?
Names can be duplicated or respelled, which breaks lookups and totals. IDs are stable.
How do I reconcile a dashboard figure?
Filter the underlying rows by the same conditions and compare the subtotal with the dashboard number.