Excel for sales reps and sales managers

Q: What is the formula for win rate?

Won deals divided by closed deals: =COUNTIF(Stage,"Won")/(COUNTIF(Stage,"Won")+COUNTIF(Stage,"Lost")). Leave open deals out of the bottom of the fraction.

For account executives, sales managers and sales ops people who live in a CRM but answer the hard questions in Excel. Export your deals and you can see the weighted forecast, each rep's quota attainment and the gap they still have to close, the real win rate, and commission under tiered plans. The examples use one quarter's pipeline for three reps, with a stage probability table and quotas beside it.

Account executives and inside sales reps
Sales managers
Sales operations and RevOps analysts
Founders who still run sales

Sample data

Every result on this page comes from this sheet, calculated by the same engine the Cellwise app uses. Change a number and watch the formulas react.

	A	B	C	D	E	F	G	H
1	Deal	Rep	Stage	Amount	Close date	Win probability	Stage	Probability
2	D-2101	Maya	Won	18500	Oct 8, 2026	1	Prospect	0.1
3	D-2102	Luis	Proposal	24000	Nov 20, 2026	0.5	Demo	0.3
4	D-2103	Priya	Won	9800	Oct 15, 2026	1	Proposal	0.5
5	D-2104	Maya	Negotiation	31000	Nov 12, 2026	0.75	Negotiation	0.75
6	D-2105	Luis	Lost	12500	Oct 22, 2026	0	Won	1
7	D-2106	Priya	Demo	15000	Dec 4, 2026	0.3	Lost	0
8	D-2107	Maya	Lost	7200	Oct 2, 2026	0
9	D-2108	Luis	Won	21400	Oct 29, 2026	1	Rep	Q4 quota
10	D-2109	Priya	Negotiation	27500	Nov 25, 2026	0.75	Maya	60000
11	D-2110	Maya	Prospect	40000	Dec 18, 2026	0.1	Luis	50000
12	D-2111	Priya	Won	16300	Oct 27, 2026	1	Priya	55000

What Excel does for sales teams

Weight the pipeline by stage

A $40,000 deal you have only just met is not worth the same as one in final negotiation. A weighted pipeline multiplies each open deal by its stage probability. Column F looks the probability up from the stage table in G:H with XLOOKUP, so you change a probability once and every deal updates.

SUMPRODUCT then multiplies amount by probability for every row and adds them up. The (C2:C12<>"Won") part keeps won deals out, because they are already booked, not forecast. Lost deals have a probability of 0, so they drop out on their own.

=SUMPRODUCT((C2:C12<>"Won")*D2:D12*F2:F12)

Result: 64375

$137,500 of open deals is worth $64,375 weighted. Most of it ($43,875) comes from the two deals in Negotiation.

Calculate a win rate that means something

Win rate is deals won divided by deals closed (won plus lost). Dividing by every deal in the sheet, open ones included, makes the rate fall every time you add a new prospect, which tells you nothing about how well you close.

Count deals for a rate per deal. For a rate by value, swap COUNTIF for SUMIF on the Amount column; big losses then count for more.

=COUNTIF(C2:C12,"Won")/(COUNTIF(C2:C12,"Won")+COUNTIF(C2:C12,"Lost"))

Result: 0.66666666667 shows as 67% with Percent format

4 won out of 6 closed: a 67% win rate. Dividing by all 11 deals would show a misleading 36%.

Quota attainment for a rep

Attainment is closed-won revenue divided by quota. SUMIFS adds the Amount column only where the rep matches and the stage is Won. Pointing at the rep name in G10 instead of typing "Maya" means you can copy the formula down the quota table.

Format the result as a percentage. Over 100% means the rep beat quota.

=SUMIFS(D2:D12,B2:B12,G10,C2:C12,"Won")/H10

Result: 0.30833333333 shows as 31% with Percent format

Maya has closed $18,500 against a $60,000 quota: 31% attainment, one month into the quarter.

How much a rep still needs to find

The question a manager actually asks is: if the open deals close at their usual rates, is the rep going to make it? Subtract what is won and the rep's weighted open pipeline from quota. What is left is the gap that new deals have to fill.

The SUMPRODUCT part is the weighted pipeline from the first job, with one more condition, (B2:B12=G10), to keep only Maya's deals.

=H10-SUMIFS(D2:D12,B2:B12,G10,C2:C12,"Won")-SUMPRODUCT((B2:B12=G10)*(C2:C12<>"Won")*D2:D12*F2:F12)

Result: 14250

$60,000 quota minus $18,500 won minus $27,250 weighted open leaves a $14,250 gap. Maya needs more pipeline, not just to close what she has.

Closed-won revenue for one rep in one month

SUMIFS takes as many conditions as you need. To limit by date, give it two conditions on the Close date column: on or after the first of the month, and on or before the last day. Build the dates with DATE() rather than typing "10/1/2026" in the criteria, which can be read differently depending on regional settings.

This is the formula behind most month-by-rep commission reports.

=SUMIFS(D2:D12,B2:B12,"Luis",C2:C12,"Won",E2:E12,">="&DATE(2026,10,1),E2:E12,"<="&DATE(2026,10,31))

Result: 21400

Luis closed $21,400 in October, all from D-2108.

Average deal size

AVERAGEIF averages the Amount column only for won deals. Average won deal size matters for planning: if your quota is $60,000 and the average win is $16,500, a rep needs about four wins a quarter.

Do not average every deal in the pipeline. Open deals are often padded early on, and lost deals skew it too.

=AVERAGEIF(C2:C12,"Won",D2:D12)

Result: 16500

The four won deals average $16,500.

What is due to close next month

Combine a date window with stage conditions to see what is expected to close in November and is still open. "<>Won" and "<>Lost" mean "is not Won" and "is not Lost".

Use the first day of the next month with < as the end of the window. It is simpler than working out whether a month has 30 or 31 days.

=SUMIFS(D2:D12,E2:E12,">="&DATE(2026,11,1),E2:E12,"<"&DATE(2026,12,1),C2:C12,"<>Won",C2:C12,"<>Lost")

Result: 82500

$82,500 across three open deals (D-2102, D-2104 and D-2109) is due to close in November.

Practice: 8 exercises

Use the sample data above. Type a formula and press Check: it is graded by a real spreadsheet engine, the way Cellwise grades lessons.

1How many deals are in the Negotiation stage?

Your formula for exercise 1

Show answer
=COUNTIF(C2:C12,"Negotiation") = 2. COUNTIF counts the cells in C2:C12 that say Negotiation: 2.
2What is the total value of won deals?

Your formula for exercise 2

Show answer
=SUMIF(C2:C12,"Won",D2:D12) = 66000. SUMIF adds the amounts on the four Won rows: $66,000.
3How much has Priya closed (won)?

Your formula for exercise 3

Show answer
=SUMIFS(D2:D12,B2:B12,"Priya",C2:C12,"Won") = 26100. D-2103 ($9,800) and D-2111 ($16,300) add up to $26,100.
4Look up the win probability for D-2104's stage in the stage table.

Your formula for exercise 4

Show answer
=XLOOKUP(C5,G2:G7,H2:H7) = 0.75. D-2104 is in Negotiation, which the table weights at 75%.
5What is D-2102 worth weighted by its probability?

Your formula for exercise 5

Show answer
=D3*F3 = 12000. $24,000 x 50% = $12,000.
6What is Luis's quota attainment so far, using his quota in H11?

Your formula for exercise 6

Show answer
=SUMIFS(D2:D12,B2:B12,G11,C2:C12,"Won")/H11 = 0.428. $21,400 won / $50,000 quota = 42.8%.
7Of the value of deals that have closed, what share was lost?

Your formula for exercise 7

Show answer
=SUMIF(C2:C12,"Lost",D2:D12)/(SUMIF(C2:C12,"Won",D2:D12)+SUMIF(C2:C12,"Lost",D2:D12)) = 0.22987164527. $19,700 lost out of $85,700 closed: 23%. Measuring by value shows whether you lose the big ones.
8Which open deal (not Won or Lost) is the largest? Return its deal ID.

Your formula for exercise 8

Show answer
=XLOOKUP(MAX(FILTER(D2:D12,(C2:C12<>"Won")*(C2:C12<>"Lost"))),D2:D12,A2:A12) = D-2110. FILTER keeps the five open amounts, MAX picks $40,000, and XLOOKUP finds D-2110. Multiplying the two conditions works as AND.

Free templates for sales teams

Guided project: Weighted pipeline and quota gap

Weight every deal by stage, roll it up by rep and see who will miss quota.

6 steps in the Cellwise app. Each step checks your formula in a real grid, with hints when you get stuck. Part of Cellwise Plus.

Open guided projects

Guides for these formulas

FAQ

How do I calculate quota attainment in Excel?

Divide closed-won revenue by quota and format as a percentage: =SUMIFS(Amount,Rep,"Maya",Stage,"Won")/Quota. Over 100% means the rep beat quota.

How do I calculate tiered commission in Excel?

Keep the tiers in a lookup table sorted by threshold. For one rate on the whole amount, =VLOOKUP(attainment,tiers,2,TRUE)*revenue. For a different rate on each slice, use SUMPRODUCT with the rate increase per tier. The free commission template on this page does both.

What is the formula for win rate?

Won deals divided by closed deals: =COUNTIF(Stage,"Won")/(COUNTIF(Stage,"Won")+COUNTIF(Stage,"Lost")). Leave open deals out of the bottom of the fraction.

How do I sum sales by rep and month in Excel?

Use SUMIFS with one condition for the rep and two for the date window: =SUMIFS(Amount,Rep,"Luis",CloseDate,">="&DATE(2026,10,1),CloseDate,"<"&DATE(2026,11,1)). A PivotTable with Rep in rows and Close date grouped by month in columns does the same for everyone at once.

Excel for other fields

Updated November 2, 2026