How to Calculate Win Rate by Sales Rep in Google Sheets
Won deals divided by decided deals, for each rep, over a fixed period. Simple formula, three common ways to get it wrong.
The short answer: win rate is won ÷ (won + lost), counted with COUNTIFS on deals that closed in the period. In the sample data, Maya Reyes has the best 2026 win rate at 59.6%; the team as a whole wins 48.3%.
The sample data
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Deal ID | Account | Deal name | Rep | Stage | Amount | Probability | Created | Close date |
| 2 | D-2201 | Foxglove Office Pantry | Foxglove Office Pantry - Supply agreement | Priya Shah | Closed Won | 21200 | 100 | 2025-09-06 | 2025-10-19 |
| 3 | D-2202 | Quarter Moon Coffee House | Quarter Moon Coffee House - Supply agreement | Maya Reyes | Closed Won | 22900 | 100 | 2025-09-07 | 2025-10-13 |
| 4 | D-2203 | Second Story Café | Second Story Café - Supply agreement | Priya Shah | Closed Lost | 15000 | 0 | 2025-09-08 | 2025-12-08 |
| 5 | D-2204 | Sparrow Café | Sparrow Café - Supply agreement | Priya Shah | Closed Lost | 13200 | 0 | 2025-09-13 | 2025-11-03 |
| 6 | D-2205 | Maple Street Kitchen | Maple Street Kitchen - Supply agreement | Maya Reyes | Closed Won | 17400 | 100 | 2025-09-14 | 2025-11-04 |
| … | 235 more rows | ||||||||
Sample data: a tab named Deals with 240 deals in a sales pipeline, as of October 31, 2026, one row per deal. Probability is the stage's win probability as a whole number (10 means 10%). Close date is the actual date for closed deals and the expected date for open ones. Download deals.csv and import it (File → Import) to follow along. The business is fictional.
The classic way: COUNTIFS by rep, stage and close date
Column D of Deals is Rep, E is Stage, F is Amount and I is Close date. On a new tab, type the reps in A2:A5. Then in row 2, copied down (B is won, C is lost, D is the win rate, E is the amount won):
=COUNTIFS(Deals!$D:$D, $A2, Deals!$E:$E, "Closed Won", Deals!$I:$I, ">="&DATE(2026,1,1), Deals!$I:$I, "<="&DATE(2026,10,31))
=COUNTIFS(Deals!$D:$D, $A2, Deals!$E:$E, "Closed Lost", Deals!$I:$I, ">="&DATE(2026,1,1), Deals!$I:$I, "<="&DATE(2026,10,31))
=IFERROR(B2/(B2+C2), "")
=SUMIFS(Deals!$F:$F, Deals!$D:$D, $A2, Deals!$E:$E, "Closed Won", Deals!$I:$I, ">="&DATE(2026,1,1), Deals!$I:$I, "<="&DATE(2026,10,31))Format D as a percent, then sort the rows by it (Data → Sort range, column D, Z → A). The All reps row sums B, C and E and divides the totals: =B6/(B6+C6).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Won | Lost | Win rate | Won amount |
| 2 | Maya Reyes | 31 | 21 | 59.6% | $300,800 |
| 3 | Priya Shah | 18 | 21 | 46.2% | $141,600 |
| 4 | Luis Garcia | 18 | 23 | 43.9% | $218,500 |
| 5 | Tom Becker | 18 | 26 | 40.9% | $233,000 |
| 6 | All reps | 85 | 91 | 48.3% | $893,900 |
Computed from the sample CSV above. Cells formatted as currency and percent.
Win rate by value. Some teams prefer dollars won over dollars decided. Add the amount lost with the same SUMIFS on "Closed Lost" and divide: won amount ÷ (won amount + lost amount). On the sample data, by value: Maya Reyes 63.7%, Tom Becker 55.7%, Luis Garcia 49.4%, Priya Shah 41.0%.
Gotchas
- Open deals in the denominator. Dividing won by all deals makes every rep with a full pipeline look bad. Only decided deals (won or lost) belong in the win rate.
- Filtering by created date. Use the close date. Filtering by created date drops deals opened last year and closed this year, and the number changes every time an old deal closes.
- Tiny samples. A rep with 4 decided deals and 3 wins is "75%". Show the counts beside the rate so nobody ranks reps on a handful of deals.
The one-cell way: =QUIRIZ
With the Quiriz add-on for Google Sheets, you type the question instead of building the formula. =QUIRIZ does not read the cells around it: it answers from the datasets in your Quiriz account. For this example, upload deals.csv as a dataset, or connect HubSpot. You can also add a Google Sheet itself as a dataset that refreshes on a schedule. Then, in any empty cell:
=QUIRIZ("win rate by sales rep for deals closed between January 1 and October 31, 2026, as won divided by won plus lost, with won and lost counts and won amount, plus an all-reps row", "table")The "table" mode spills the answer down and to the right from that cell, header row first:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Won | Lost | Win rate | Won amount |
| 2 | Maya Reyes | 31 | 21 | 59.6% | $300,800 |
| 3 | Priya Shah | 18 | 21 | 46.2% | $141,600 |
| 4 | Luis Garcia | 18 | 23 | 43.9% | $218,500 |
| 5 | Tom Becker | 18 | 26 | 40.9% | $233,000 |
| 6 | All reps | 85 | 91 | 48.3% | $893,900 |
Same figures as the formula result above, from the same sample data. The spilled cells are plain numbers, so you can format them, total them or chart them; here they are formatted as currency and percent. Column headings are written with each answer and may be worded differently.
The other modes: "verbose" (the default) answers in a sentence, "bullets" writes two to four points one per cell, and "notes" adds how the figure was calculated. Install Quiriz for Google Sheets and try the question on your own data.
Ask the question, get the table
Quiriz answers business questions in a Google Sheets cell, from your imported files, scheduled Google Sheets and connected tools. Free to start.
Install for Google Sheets →Free on the Google Workspace Marketplace. Or try it on sample data first, no signup.