Weighted pipeline for one stage (SUMPRODUCT)
The problem
Problem statement
A forecast is not the size of the pipeline, it is the size times the probability. Amounts and probabilities are on each row of A2:D6, and the stage to forecast is F1. Put the weighted value of that stage in the Weighted cell F2.
One formula must multiply amount by probability per row and add the rows — no helper column.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Account | Stage | Amount | Probability | Stage | Negotiation |
| 2 | Acme | Negotiation | 50000 | 0.6 | Weighted | |
| 3 | Bolt | Proposal | 30000 | 0.3 | ||
| 4 | Cobalt | Negotiation | 20000 | 0.6 | ||
| 5 | Dana | Discovery | 40000 | 0.1 | ||
| 6 | Echo | Proposal | 10000 | 0.3 |
Notes
SUMPRODUCT multiplies matching positions and sums the products, which is exactly a weighted total. The stage test becomes a TRUE/FALSE array, and multiplying by it zeroes the rows you do not want. Summing the stage first and multiplying once is the classic wrong answer: probabilities differ per row.
How to approach
Your answer goes in F2.
- Target cell is F2
- Build the stage test as an array: B2:B6 = F1
- Multiply by the amount and the probability arrays
- Shape: =SUMPRODUCT((?=?)*?*?)
FAQ
How do I calculate a weighted pipeline?
Multiply each deal's amount by its probability and add them: =SUMPRODUCT((stage range=stage)*amount range*probability range). Never sum the stage and multiply once — probabilities differ per row.
Why does my weighted forecast look too high?
Either the probability column is stored as text (so multiplication fails or is skipped), or the stage filter is missing and every stage is included.