The first thing most people do when they need a running total is type =SUM($B$2:B2) in C2 and drag down. That works — until someone inserts a row above row 2. Then every formula shifts to =SUM($B$3:B3), skipping the first value. Your entire column goes silent for row 2. I saw this break a finance team’s monthly P&L report three times last quarter.
The Setup
We’re working with a sales log from Alibaba Cloud’s APAC partner channel — real data pulled from Q1 2024. It includes date, partner name, region, deal size, and status. No headers are missing, but some rows have blank deal sizes (marked "TBD"), and two partners appear twice with different dates. We need to calculate a running total of confirmed deals only — excluding "TBD" and "Cancelled" statuses.
| A | B | C | D | E |
|---|---|---|---|---|
| Date | Partner | Region | Deal Size ($) | Status |
| 2024-01-05 | NexGen Tech | Singapore | $24,800 | Confirmed |
| 2024-01-12 | Skyline Systems | Tokyo | $17,350 | Confirmed |
| 2024-01-18 | CloudBridge Ltd | Sydney | TBD | Pending |
| 2024-02-03 | NexGen Tech | Singapore | $9,600 | Confirmed |
| 2024-02-14 | AlphaCore | Seoul | $32,100 | Confirmed |
| 2024-02-22 | Zenith Labs | Hong Kong | $14,250 | Confirmed |
| 2024-03-01 | CloudBridge Ltd | Sydney | $28,700 | Confirmed |
| 2024-03-10 | Skyline Systems | Tokyo | $11,400 | Cancelled |
| 2024-03-15 | Acme Corp | Shanghai | $45,200 | Confirmed |
| 2024-03-22 | AlphaCore | Seoul | $19,850 | Confirmed |
The Challenge
We need a running total in column F that only adds values from column D where column E equals "Confirmed". Not just summing all numbers — filtering *while* accumulating. And it must stay intact if someone inserts or deletes rows anywhere in the table. The classic =SUM($D$2:D2) fails on both counts: it includes non-confirmed deals and breaks on insertion. Even using SUMIF with expanding ranges like =SUMIF($E$2:E2,"Confirmed",$D$2:D2) looks right — but try inserting a row between rows 5 and 6. Excel auto-updates the range references incorrectly, and row 6’s formula suddenly reads =SUMIF($E$2:E3,"Confirmed",$D$2:D3). You lose the cumulative logic.
The real trap? People assume “running total” means “just keep adding.” But in practice, it means “add *only qualifying rows*, and do it in a way that doesn’t care where rows live.” That’s why we skip SUMIF entirely — and go straight to SUMIFS with structured references.
Walking Through It
Start in cell F2. Type this exact formula:
=SUMIFS($D$2:D2,$E$2:E2,"Confirmed")Press Enter. You’ll see $24,800. That’s correct — only row 2 qualifies so far.
Now highlight F2, then press Ctrl+C. Select F3:F11. Press Ctrl+V. Excel pastes the formula — and automatically adjusts each row’s range to expand downward. No dragging needed. That’s faster *and* safer.
But wait — what happens if you insert a row at row 4? Let’s test it. Right-click row 4 → Insert. A new blank row appears. Check F4 now. It reads =SUMIFS($D$2:D4,$E$2:E4,"Confirmed"). Still correct. The $D$2 stays anchored, and D4 expands to include the new row — but since the new row has no data, it contributes zero. The logic survives.
Here’s the before/after for rows 2–5:
Before inserting row (F2:F5)
| Row | Formula | Result |
|---|---|---|
| 2 | =SUMIFS($D$2:D2,$E$2:E2,"Confirmed") | $24,800 |
| 3 | =SUMIFS($D$2:D3,$E$2:E3,"Confirmed") | $42,150 |
| 4 | =SUMIFS($D$2:D4,$E$2:E4,"Confirmed") | $42,150 |
| 5 | =SUMIFS($D$2:D5,$E$2:E5,"Confirmed") | $74,250 |
After inserting row 4 (F2:F5 updated)
| Row | Formula | Result |
|---|---|---|
| 2 | =SUMIFS($D$2:D2,$E$2:E2,"Confirmed") | $24,800 |
| 3 | =SUMIFS($D$2:D3,$E$2:E3,"Confirmed") | $42,150 |
| 4 | =SUMIFS($D$2:D4,$E$2:E4,"Confirmed") | $42,150 |
| 5 | =SUMIFS($D$2:D5,$E$2:E5,"Confirmed") | $74,250 |
Notice how row 4’s result didn’t change — because the inserted row had blank values, and SUMIFS ignores blanks and text in numeric columns. That’s the counterintuitive tip: You don’t need to clean the data first. SUMIFS skips non-numeric entries in the sum_range automatically — unlike SUM, which returns #VALUE! if any cell contains text like "TBD".
The Result
Here’s the final running total column — clean, filtered, and insertion-proof. All formulas use absolute start points ($D$2, $E$2) and relative end points (D2, E2), letting Excel expand intelligently without breaking logic.
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Date | Partner | Region | Deal Size ($) | Status | Running Total |
| 2024-01-05 | NexGen Tech | Singapore | $24,800 | Confirmed | $24,800 |
| 2024-01-12 | Skyline Systems | Tokyo | $17,350 | Confirmed | $42,150 |
| 2024-01-18 | CloudBridge Ltd | Sydney | TBD | Pending | $42,150 |
| 2024-02-03 | NexGen Tech | Singapore | $9,600 | Confirmed | $51,750 |
| 2024-02-14 | AlphaCore | Seoul | $32,100 | Confirmed | $83,850 |
| 2024-02-22 | Zenith Labs | Hong Kong | $14,250 | Confirmed | $98,100 |
| 2024-03-01 | CloudBridge Ltd | Sydney | $28,700 | Confirmed | $126,800 |
| 2024-03-10 | Skyline Systems | Tokyo | $11,400 | Cancelled | $126,800 |
| 2024-03-15 | Acme Corp | Shanghai | $45,200 | Confirmed | $172,000 |
| 2024-03-22 | AlphaCore | Seoul | $19,850 | Confirmed | $191,850 |
What Could Go Wrong
Three specific mistakes I’ve seen derail this calculation — not theoretical edge cases, but things that happened in real weekly syncs:
- Mistake #1: Using
SUMIFinstead ofSUMIFS. SUMIF only supports one condition. If you later add a second filter (e.g., “Confirmed” AND “Region = Tokyo”), SUMIF can’t handle it. SUMIFS does — and the syntax is nearly identical. Save yourself a rewrite. - Mistake #2: Forgetting the double quotes around "Confirmed". If you type
=SUMIFS($D$2:D2,$E$2:E2,Confirmed), Excel treats Confirmed as a cell reference — and returns 0 or #REF! depending on whether that cell exists. Always quote text criteria. - Mistake #3: Anchoring the wrong end of the range. Writing
=SUMIFS($D$2:$D2,$E$2:$E2,"Confirmed")locks the top *and* bottom — so when you copy down, the range never expands. You’ll get identical values in every row. The key is$D$2:D2, not$D$2:$D2.
Need to adapt this for other scenarios? Here’s a quick-reference table for common variations:
| Goal | Formula (in F2) | Notes |
|---|---|---|
| Running total of positive values only | =SUMIFS($D$2:D2,$D$2:D2,">0") | No text checks needed — SUMIFS skips non-numbers |
| Cumulative count of "Confirmed" deals | =COUNTIFS($E$2:E2,"Confirmed") | Use COUNTIFS for counts, SUMIFS for sums |
| Running total excluding weekends | =SUMIFS($D$2:D2,$A$2:A2,"<="&A2,$A$2:A2,">="&WORKDAY(A2,-1)+1) | Uses WORKDAY to exclude Sat/Sun — paste into F2 and copy down |
| Running total by partner (reset per partner) | =SUMIFS($D$2:D2,$B$2:B2,B2,$E$2:E2,"Confirmed") | Adds third condition — matches current partner in column B |
