How to Summarize Your Sales Pipeline by Stage in Google Sheets
How many open deals sit in each stage, what they are worth, and what they are worth weighted by the odds of closing, in funnel order.
The short answer: list your stages in funnel order, then =COUNTIF(Deals!$E:$E, $A2) and =SUMIFS(Deals!$F:$F, Deals!$E:$E, $A2) beside each. In the sample data, the open pipeline on October 31, 2026 is 42 deals worth $401,400, or $158,315 weighted.
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: a stage list with COUNTIF, SUMIFS and SUMPRODUCT
Column E of Deals is Stage, F is Amount and G is Probability (a whole number, 10 for 10%). On a new tab, type the open stages in A2:A5 in funnel order: Lead, Qualified, Proposal, Negotiation. Then in row 2, copied down:
=COUNTIF(Deals!$E:$E, $A2)
=SUMIFS(Deals!$F:$F, Deals!$E:$E, $A2)
=SUMPRODUCT((Deals!$E$2:$E=$A2) * Deals!$F$2:$F * Deals!$G$2:$G) / 100B counts the deals in the stage, C adds their amounts, and D is the weighted amount: each deal's amount times its probability. Drop the / 100 if your probability column holds percents like 10%. The Total row sums each column.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Stage | Open deals | Amount | Weighted |
| 2 | Lead | 10 | $92,900 | $9,290 |
| 3 | Qualified | 13 | $120,300 | $30,075 |
| 4 | Proposal | 10 | $88,800 | $44,400 |
| 5 | Negotiation | 9 | $99,400 | $74,550 |
| 6 | Total | 42 | $401,400 | $158,315 |
Computed from the sample CSV above. Cells formatted as currency and percent.
Why not QUERY? =QUERY(Deals!A:I, "select E, count(A), sum(F) where E <> 'Closed Won' and E <> 'Closed Lost' group by E", 1) gives the same counts and amounts, but sorted alphabetically (Lead, Negotiation, Proposal, Qualified), which reads as nonsense for a funnel. Typing the stages yourself keeps the order.
Gotchas
- Closed deals in the total. A pipeline is open deals only. Leave Closed Won and Closed Lost off the stage list, or the "pipeline" includes revenue you already have and deals you already lost.
- Stale deals. Deals whose expected close date has passed still count here. Add
Deals!$I:$I, ">="&TODAY()to the SUMIFS to see only deals still expected to close. - Weighted is not a forecast. Stage probabilities are averages. Ten 50% deals do not reliably produce five wins this month. See weighted pipeline is not a forecast.
- Stage names must match exactly. "Proposal " with a trailing space counts as zero. Copy the names from the data, or use a dropdown in the CRM export's stage column.
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("open sales pipeline by stage as of October 31, 2026: number of deals, total amount and weighted amount, in funnel order from Lead to Negotiation, with a total row", "table")The "table" mode spills the answer down and to the right from that cell, header row first:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Stage | Open deals | Amount | Weighted |
| 2 | Lead | 10 | $92,900 | $9,290 |
| 3 | Qualified | 13 | $120,300 | $30,075 |
| 4 | Proposal | 10 | $88,800 | $44,400 |
| 5 | Negotiation | 9 | $99,400 | $74,550 |
| 6 | Total | 42 | $401,400 | $158,315 |
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.