Win rate over closed deals only (COUNTIF ratio)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
Every deal in A2:B7 carries an outcome: Won, Lost or Open. Put the win rate of the *closed* deals in the Win rate cell D2, as a decimal.
Open deals are not losses — they must not appear in either side of the ratio.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | |
|---|---|---|---|
| 1 | Deal | Outcome | Win rate |
| 2 | D-101 | Won | |
| 3 | D-102 | Lost | |
| 4 | D-103 | Won | |
| 5 | D-104 | Open | |
| 6 | D-105 | Lost | |
| 7 | D-106 | Won |
Notes
A win rate is won ÷ (won + lost). Including Open deals in the denominator is the classic error: it makes the rate look worse the fuller the pipeline is. COUNTIF counts each outcome, and the two counts have to be added before dividing.
How to approach
Your answer goes in D2.
- Target cell is D2
- Count the Won rows and the Lost rows separately
- The denominator is won + lost, not every row
- Shape: =COUNTIF(?, ?) / (COUNTIF(?, ?) + COUNTIF(?, ?))
Function guide
FAQ
How do I calculate win rate in Excel?
Won ÷ (won + lost): =COUNTIF(outcome, "Won") / (COUNTIF(outcome,"Won") + COUNTIF(outcome,"Lost")). Leave open deals out.
Why is my win rate lower than the team reports?
Open deals are probably counted as losses. A rate that falls as the pipeline fills is the symptom.