Home / Blog / Win Rate by Rep

How-to · 2026

Win Rate by Sales Rep

The breakdown every sales meeting asks for. How to build it from an export, the two ways it silently misleads, and the check that takes ten seconds and catches both.

By Ed · Published August 20, 2026 · 8 min read

Twenty-five years starting companies, consulting, owning a multi-million-dollar P&L and leading business intelligence for large enterprises.

Win rate by rep is the most-requested breakdown in sales reporting, it takes about four minutes to build, and it fails in two specific ways that are both invisible in the finished table. Neither is about arithmetic.

Building it

Start from the definition, which needs settling first: won deals over closed deals, counts, open deals excluded. If that is not already agreed, the breakdown will inherit an argument — the three definitions and their consequences are in why your win rate keeps changing.

With a deal export in Deals and rep names in column A:

Won     =COUNTIFS(Deals[Stage],"Closed Won",Deals[Owner],$A2)
Closed  =COUNTIFS(Deals[Stage],"Closed Won",Deals[Owner],$A2)
        +COUNTIFS(Deals[Stage],"Closed Lost",Deals[Owner],$A2)
Rate    =IFERROR(B2/C2,"no closed deals")

Build the rep list in column A from the unique values in the owner column — =UNIQUE(Deals[Owner]) — rather than typing it. A typed list is correct on the day it is written and silently wrong the first time someone joins.

Failure one: reps who are not in the table

The dangerous version of this report is not one with a wrong number. It is one with a missing row, because a missing row looks like nothing at all.

Three ways it happens:

The rep list is stale. Anyone who joined after the sheet was built has no row.
The owner field is blank on some deals. Those deals group under nothing, so they are in no rep's row and in no visible total.
The table was built from wins. A rep with eleven losses and no wins has no won rows to be found by, so they do not appear — and they are the single most important row on the page.

One check catches all three:

=SUM(C2:C40)=COUNTIFS(Deals[Stage],"Closed Won")+COUNTIFS(Deals[Stage],"Closed Lost")

If the closed deals in your table do not sum to the closed deals in the export, someone is missing. Ten seconds, and it is the only thing standing between you and a confident, incomplete ranking.

Failure two: ranking on numbers too small to rank

A quarter rarely gives a rep enough closed deals to distinguish them from each other.

Rep      Won   Closed   Rate
A          2        5    40%
B          9       21    43%
C          1        4    25%

Rep C is bottom of the table. Rep C also closed four deals; one more win makes it 50% and moves them to the top. Nothing about Rep C changed, and the ordering of this table is mostly noise.

The defence is not statistical sophistication. It is putting the closed count next to the rate, always, so a reader can see what the percentage is made of. A rate without its denominator invites a ranking, and a ranking on four deals is a coin toss that someone is about to act on.

From building the table that got read out loud

We shipped a version of the missing-rep bug ourselves. Asking our own tool for the breakdown one way and asking a slightly different way gave different routes through the data, and the looser one dropped two reps of five while returning a table that looked entirely correct — right shape, plausible percentages, nothing amiss on the page.

That is the lesson worth taking, whether you use a pivot or an AI tool: the answer that looks right and the answer that is right are indistinguishable by inspection. The only defence is checking that the parts sum to the whole. It costs one formula and it is the reason our answers now say which definition produced them.

Owner is not always who you think

In many CRMs the owner is set when the deal is created and never revisited, so the field records who sourced the deal rather than who closed it. In others it moves on handover. Both are legitimate and they produce different tables from the same export.

Worth knowing which yours does before anyone is measured on it. Where a team splits sourcing and closing, one owner field cannot represent both, and the honest table says which one it is showing.

Breakdowns that are usually more useful

Rep-level win rate is what gets asked for. Three others are frequently more actionable and less noisy, because their denominators are bigger:

By source. Which lead sources produce winnable deals. Actionable at the marketing end.
By deal type. New business versus expansion behave completely differently and averaging them hides both.
By loss reason. Not a win rate at all, and often the most useful table in the set — provided blank reasons are shown rather than dropped.

Asking for it instead of building it

Quiriz for CRM holds win rate as one definition and breaks it down by rep, team, region, source, deal type or loss reason without a new pivot each time. In Excel that is =QUIRIZ.ASK("win rate by sales rep this quarter", "table").

Deals with no owner, no source or no loss reason recorded are bucketed into a visible row rather than dropped from the breakdown, so a table always ties back to the ungrouped total. That is the fix for failure one, applied by default.

The honest boundary: we cannot make a five-deal quarter significant. The count comes back beside the rate for exactly that reason, and a small sample is still a small sample no matter how it is computed.

Check that your breakdown's closed deals sum to the export's closed deals before anyone reads it. A missing rep looks like nothing, and nothing is the hardest thing to notice.

Get the breakdown without the pivot

Upload your CRM export and ask for win rate by rep, source or deal type in plain English. Free to start.

Try Quiriz free →

Frequently asked questions

How do I calculate win rate by sales rep in Excel?

Two COUNTIFS per rep — won deals and closed deals, both filtered on the owner — then divide. Build the rep list from the unique values in the owner column rather than typing it, or anyone who joined recently will be missing from the table without any indication.

Why are some reps missing from my win rate table?

Usually one of three things. The rep list was typed by hand and is out of date. The owner field is blank on some deals, so those rows group under nothing and vanish. Or the table was built from wins, so a rep with losses and no wins has no row to appear in — which is exactly the rep you most need to see.

How many deals do you need before win rate by rep means anything?

More than most quarters provide. A rep who closed five deals and won two shows 40%, and one more win either way moves that to 60% or 20%. Nothing about the person changed. Show the closed-deal count in the table and treat anything under roughly twenty as directional rather than as a ranking.

Should I use deal owner or the rep who did the work?

Whichever your CRM records consistently, and say which. In many teams the owner is set at creation and never changes, so it reflects who sourced the deal rather than who closed it. In others it moves on handover. Both are defensible and they produce different tables from the same export, so the label matters more than the choice.

Why does my AI tool give a different breakdown than my pivot?

Because "by sales rep" and "by deal owner" can resolve to different fields, and a tool that does not tell you which one it used has left you to guess. Ask for the definition alongside the answer. A breakdown that omits reps without saying so looks exactly like a correct one, and the only defence is checking that the parts sum to the total.