Cellwise Start free

Excel guide · 3 min read

How to calculate a weighted pipeline in Excel

Quick answerWeighted pipeline is the sum of each open deal's amount times the probability of its stage. Look up the probability from a stage table with =XLOOKUP(C2,$I$2:$I$7,$J$2:$J$7), multiply by the amount in G2 with =D2*F2, then add up only open deals with a close date still ahead: =SUMIFS(G2:G11,C2:C11,"<>Won",C2:C11,"<>Lost",E2:E11,">="&M1).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1DealOwnerStageAmountClose dateProbabilityWeightedStageProbabilityReport date
2D-201MayaProposal48000Nov 20, 20260.524000Prospect0.1Quarter end
3D-202LiamQualified30000Dec 12, 20260.257500Qualified0.25Quota still to close
4D-203MayaNegotiation22000Nov 28, 20260.7516500Proposal0.5
5D-204AnaWon35000Nov 3, 2026135000Negotiation0.75
6D-205LiamProspect60000Jan 15, 20270.16000Won1
7D-206AnaProposal18000Oct 30, 20260.59000Lost0
8D-207MayaLost40000Nov 6, 202600
9D-208AnaNegotiation52000Dec 5, 20260.7539000
10D-209LiamProposal26000Dec 19, 20260.513000
11D-210MayaQualified15000Nov 25, 20260.253750

Download this sheet as an Excel practice file (.xlsx)

Put stage probabilities in a table, not in formulas

Keep a small table with each stage and its probability (I1:J7 in the sample). The numbers here are placeholders; use your own team's history, for example the share of deals that reached Proposal and went on to be won last year. Because they live in cells, sales leadership can change them and every deal updates.

For each deal, F2 is =XLOOKUP(C2,$I$2:$I$7,$J$2:$J$7) and G2 is =D2*F2. If someone types a stage that isn't in the table, like "Closed won", XLOOKUP returns #N/A. That's useful: it tells you the CRM export uses different names. Add a fourth argument, =XLOOKUP(C2,$I$2:$I$7,$J$2:$J$7,"Check stage"), if you'd rather see a label, but don't swap in 0, which quietly drops the deal from the forecast.

=G2

Result: 24000

24000: D-201 is 48,000 at Proposal, weighted at 50%.

Count only open deals with a future close date

=SUM(G2:G11) gives 153,750, but that includes the Won deal at 100% (35,000 already closed) and adds nothing useful from Lost deals at 0%. Won revenue belongs in actuals, not in the forecast.

It also includes D-206, a Proposal whose close date of October 30 is before the report date in M1. A deal that should have closed and didn't isn't 50% likely any more. Exclude past-due close dates with a third condition and fix them in the CRM. The report date sits in a cell so the same sheet works next week; =TODAY() works too if you want it to move by itself.

=SUMIFS(G2:G11,C2:C11,"<>Won",C2:C11,"<>Lost",E2:E11,">="&M1)

Result: 109750

109750: the weighted value of the seven open deals that can still close on time.

List the stale deals to clean up

In Excel 365 or 2021, FILTER pulls out every open deal whose close date has already passed. Multiplying the conditions means all must be TRUE. Send this list to the owners before the forecast call: each deal either gets a new close date or moves to Lost.

=FILTER(A2:A11,(E2:E11<M1)*(C2:C11<>"Won")*(C2:C11<>"Lost"))

Result: D-206

D-206: the only open deal with a close date before November 11.

Weighted pipeline by expected close month

Add date limits to the same SUMIFS. For the rest of the current month: =SUMIFS(G2:G11,C2:C11,"<>Won",C2:C11,"<>Lost",E2:E11,">="&M1,E2:E11,"<="&EOMONTH(M1,0)) gives 44,250. For December, change the dates to ">"&EOMONTH(M1,0) and "<="&EOMONTH(M1,1): 59,500. January has just the 60,000 Prospect, weighted to 6,000.

Swap the owner in as a condition, B2:B11,"Maya", for the same view per rep: Maya 44,250, Ana 39,000, Liam 26,500. The sales teams hub and the free sales pipeline tracker template use the same layout.

=SUMIFS(G2:G11,C2:C11,"<>Won",C2:C11,"<>Lost",E2:E11,">="&M1,E2:E11,"<="&M2)

Result: 103750

103750: weighted pipeline expected to close by the quarter end on December 31.

Pipeline coverage: is there enough to hit quota?

Coverage compares open pipeline (unweighted) with the quota still to close: =SUMIFS(D2:D11,C2:C11,"<>Won",C2:C11,"<>Lost",E2:E11,">="&M1,E2:E11,"<="&M2)/M3. Here 193,000 of open deals in the quarter against 120,000 still needed is 1.61x. Many teams look for 3x or more early in a quarter, but the right multiple depends on your own win rate, so treat any rule of thumb as a starting point.

Count only deals that close inside the period. Including D-205 (January) pushes coverage to 2.11x and makes the quarter look safer than it is. The weighted number tells the same story from the other side: 103,750 weighted against 120,000 needed leaves a gap of 16,250 that new deals or faster closes have to cover. For progress against target, see quota attainment.

=SUMIFS(D2:D11,C2:C11,"<>Won",C2:C11,"<>Lost",E2:E11,">="&M1,E2:E11,"<="&M2)/M3

Result: 1.6083333333 shows as 1.61 with Number format

1.6083...: 1.61x coverage for the rest of the quarter.

Common mistakes

Including Won deals in the weighted pipeline

Exclude Won and Lost with "<>Won" and "<>Lost"; report won revenue separately.

Keeping deals with close dates in the past

Add E2:E11,">="&report_date and use FILTER to list stale deals for owners to update.

Hard-coding 0.5 in the formula

Look probabilities up from a stage table so they can change in one place.

Coverage from the whole pipeline

Only count open deals whose close date falls inside the period you're measuring.

FAQ

How do you calculate weighted pipeline in Excel?

Multiply each deal's amount by its stage probability, looked up from a stage table, then SUMIFS the weighted column for open deals: =SUMIFS(G2:G11,C2:C11,"<>Won",C2:C11,"<>Lost").

What is the difference between pipeline and weighted pipeline?

Pipeline is the full value of open deals. Weighted pipeline discounts each deal by how likely its stage is to close, so it's closer to an expected value.

How do I calculate pipeline coverage in Excel?

Divide the open pipeline that closes within the period by the quota still to close: =open_pipeline/quota_remaining. 1.6 means 1.6x coverage.

Can I do a weighted pipeline without a helper column?

Yes: =SUMPRODUCT((C2:C11<>"Won")*(C2:C11<>"Lost")*(E2:E11>=M1),D2:D11,F2:F11) gives the same 109,750, though the helper column is easier to check.

Functions used

Related guides

Updated