How to calculate win rate in Excel

Won divided by closed, not by every deal

Export your deals with the rep, the source, the created and closed dates, the stage and the amount. Most CRMs use stages like "Closed Won" and "Closed Lost" for finished deals and other names for open ones.

The asterisk in "Closed*" is a wildcard: it counts every stage that starts with Closed, so won and lost together. 6 of the 11 closed deals were won: 54.5%. Dividing by all 14 deals gives 42.9%, and that number drops every time someone adds a new prospect, even if nobody loses anything.

If your stages are just "Won" and "Lost", use =COUNTIFS(F2:F15,"Won")/(COUNTIFS(F2:F15,"Won")+COUNTIFS(F2:F15,"Lost")). Run =UNIQUE(F2:F15) first to see every stage spelling in the export. "Closed won " with a trailing space or a "Closed - Won" variant quietly drops out of the count.

Win rate by rep

Add a rep condition to both counts. With rep names down a column, point at the name cell instead of typing it, so one formula fills the table. Maya won 3 of 4 (75%), Priya 2 of 4 (50%) and Luis 1 of 3 (33%).

Always put the number of closed deals next to the rate (=COUNTIFS(B2:B15,"Luis",F2:F15,"Closed*") gives 3). With three or four deals each, one more win changes a rep's rate by 20 points or more. Judge reps on a few quarters, not one, and look at what kind of deals each one works: two of Luis's three closed deals were outbound, which this team rarely wins.

Win rate by lead source

Swap the rep column for the source column. Inbound deals won 4 of 5 (80%), referrals 2 of 2, and outbound 0 of 4. That's the kind of gap that changes where you spend time, but check the size first: two referral deals don't prove a 100% rate.

In a grid with sources down column M and reps across row 1, add both conditions: =COUNTIFS(C2:C15,$M2,B2:B15,N$1,F2:F15,"Closed Won")/COUNTIFS(C2:C15,$M2,B2:B15,N$1,F2:F15,"Closed*"). Wrap it in IFERROR(...,"") because many combinations will have no closed deals and would show #DIV/0!.

Win rate for a quarter: by close date or by created date

"Our Q3 win rate" can mean two things. By close date, it's the deals that finished in Q3: 3 won of 8 closed, 37.5%. That answers "how did we do this quarter?" and it's what most sales reports show.

By created date, it's the deals that started in Q3: 3 of the 5 that have closed so far were won, 60%. That answers "how good were the leads we got in Q3?". It keeps changing until every Q3 deal is closed (two are still open here), so label it as preliminary.

Both use the quarter dates from K2 and K3. Change the date column in the conditions from E (Closed) to D (Created) to switch.

Value-weighted win rate

Count-based win rate treats a $4,800 deal and a $22,000 deal the same. Weight by value with SUMIFS on the Amount column: $48,500 won of $119,800 closed is 40.5%, well below the 54.5% count rate.

The gap says this team wins small deals and loses big ones: the average won deal is $8,083 and the average lost deal is $14,260. Split by size to confirm it: deals of $10,000 or more won 1 of 4 (25%), smaller deals won 5 of 7 (71%), using an extra condition like G2:G15,">=10000". Use the value rate for forecasting revenue and the count rate for coaching.

Average sales cycle for won deals

Add a Cycle days column: =IF(E2="","",E2-D2) gives days from created to closed and stays blank for open deals. AVERAGEIFS then averages it for won deals only: about 40.8 days. Lost deals took 54 days on average.

The median of won deals is 40.5 days (=MEDIAN(FILTER(H2:H15,F2:F15="Closed Won")) in Excel 365), close to the average, so no single deal is distorting it. A deal open much longer than your usual won cycle, like one created in early September and still in Negotiation, is worth a look: long deals here have usually been lost.