How to calculate quota attainment in Excel

Step 1: Closed-won revenue per rep for the quarter

Export your deals with the rep, the close date, the stage and the amount. Put the quotas in a small table next to them (G1:I5): one row per rep with the quarterly quota and a ramp % for new reps.

SUMIFS adds the Amount column where the rep matches, the stage is Won and the close date falls in the quarter (N2 to N3). Point at the rep's name cell, not a typed name, so one formula fills the table: =SUMIFS($E$2:$E$14,$B$2:$B$14,G2,$D$2:$D$14,"Won",$C$2:$C$14,">="&$N$2,$C$2:$C$14,"<="&$N$3).

Leave out the date conditions and Maya gets credit for a $9,200 deal that closed on September 29, in last quarter. That takes her from 33.8% to 49.2%. Every attainment number needs a date range, and lost or open deals (Luis's $16,000 proposal) never count.

Step 2: Divide by the quota with XLOOKUP

XLOOKUP finds each rep's quota in the quota table, so you never retype it. Divide closed-won by it and format as a percentage. Here Maya is at 33.8%, Luis 28.0%, Priya 61.1% and Sam 69.5%. Over 100% means the rep beat quota.

If a rep's name isn't in the quota table, XLOOKUP returns #N/A. Give it a fourth argument, =XLOOKUP(G2,$G$2:$G$5,$H$2:$H$5,"No quota"), so a missing quota shows up as a label rather than an error. A name typed slightly differently in the CRM ("Priya S." vs "Priya") is the usual cause.

Ramped quotas for new reps

A rep in their first months usually carries a reduced quota while they build a pipeline. Keep the full quota in one column and the ramp in another (Sam is at 50% this quarter), then divide by quota x ramp: =J5/(H5*I5).

Sam's $19,100 is 69.5% of his ramped $27,500, but only 34.7% of the full $55,000. Both numbers are true; the ramped one is fair to Sam and the full one shows how far he still has to grow. Label which one your report shows. The ramp % is your company's own plan, so it lives in an input cell, not in the formula.

Team attainment: pooled, not averaged

There are two ways to get one number for the team, and they disagree. Pooled attainment adds all closed-won and divides by all quota: $87,000 / $192,500 = 45.2%. The average of the four rates is 48.1%.

The average treats Sam's ramped $27,500 quota the same as Maya's $60,000, so his high rate pulls the team up. Pooled attainment answers "how much of the number did the team close?", which is what a manager is asked. SUMPRODUCT(H2:H5,I2:I5) gives the total ramped quota; against the full $220,000 the team is at 39.5%.

Pacing: are reps on track for the quarter?

33.8% in early November could be fine or worrying. Compare attainment with the share of the quarter gone: (as-of date - start + 1) / (end - start + 1). On November 6 (N4), 37 of 92 days have passed: 40.2%.

Divide attainment by that share for a pace index. 1.0 is exactly on pace. Maya is at 0.84, Luis 0.70, Priya 1.52 and Sam 1.73. To see what's left, the gap is quota x ramp - closed-won: Maya needs $39,700 more, about $722 per remaining day over 55 days.

In a live sheet, use TODAY() in place of N4. Remember that many deals close in the last weeks of a quarter, so a straight-line pace runs behind early on. Compare with last quarter's pace on the same date before calling a rep behind.