Quota attainment for one rep (SUMIFS ÷ quota)
The problem
Problem statement
Bookings are logged per rep in A2:C7, and the rep to report is F1 with their quota in F2. Put that rep's attainment — bookings divided by quota, as a decimal — in the Attainment cell F3.
The rep and the quota both come from cells; the same formula has to answer for any rep.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | E | F | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Amount | Rep | Ada |
| 2 | Ada | East | 500 | Quota | 1000 |
| 3 | Ben | West | 300 | Attainment | |
| 4 | Ada | East | 200 | ||
| 5 | Cleo | North | 400 | ||
| 6 | Ada | West | 100 | ||
| 7 | Ben | West | 250 |
Notes
SUMIFS collapses the booking log to one number per rep, and dividing by the quota turns it into attainment. 0.8 means 80% — keep it as a decimal and format the cell, rather than writing "80%" as text.
How to approach
Your answer goes in F3.
- Target cell is F3
- Total the bookings for F1 first
- Divide that by F2
- Shape: =SUMIFS(?, ?, ?) / ?
Function guide
FAQ
How do I calculate quota attainment?
Bookings for the rep divided by the quota: =SUMIFS(amounts, reps, rep) / quota. Keep the result as a decimal and format it as a percentage.
Why does my attainment look like 80 instead of 0.8?
The quota was probably entered as 10 instead of 1000, or the formula multiplies by 100. Divide once, and let the number format show the percent sign.