Running balance per account (expanding SUMIFS)
The problem
Problem statement
The ledger mixes two accounts in one column: A2:A7 holds the account, B2:B7 the date and C2:C7 the amount. The row for 2026-01-12 needs its balance to date — everything on that same account from the top of the sheet down to that row. Put it in the Running balance cell D7.
The start of the range must not move when the formula is copied down; only the end grows.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Account | Date | Amount | Running balance |
| 2 | CASH | 2026-01-02 | 100 | |
| 3 | CASH | 2026-01-05 | 250 | |
| 4 | BANK | 2026-01-05 | 500 | |
| 5 | CASH | 2026-01-08 | -80 | |
| 6 | BANK | 2026-01-10 | 300 | |
| 7 | CASH | 2026-01-12 | 40 |
Notes
A running total needs a range that grows: $C$2:C7 — the start is locked with $, the end is relative. Adding the account criterion makes it a running total per account, which is what a ledger actually shows. Summing the whole column would give one balance for every row.
How to approach
Your answer goes in D7. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is D7
- The sum range should end at the current row: $C$2:C7
- Criteria: account range $A$2:A7 against A7
- Shape: =SUMIFS(?, ?, ?) — which half is locked?
Official solutions
Show 2 solutions — try it yourself first
Approach 1: expanding SUMIFS
=SUMIFS($C$2:C7, $A$2:A7, A7)
Only CASH rows up to row 7 count: 100 + 250 - 80 + 40 = 310. BANK rows are skipped because the criteria range ends at the current row and must equal A7.
Approach 2: SUMIF with the same expanding pair
=SUMIF($A$2:A7, A7, $C$2:C7)
Same expanding ranges, SUMIF argument order. Whichever you use, the two ranges must expand in step or the account and amount fall out of alignment.
Function guide
FAQ
How do I make a running total that resets per group?
Grow the ranges from a locked first row: =SUMIFS($C$2:C7, $A$2:A7, A7). Copying down moves only the end of each range, so each row totals its group up to itself.
Why does my running total show the same number on every row?
The ranges are fully absolute ($C$2:$C$7), so every cell sums the whole column. Leave the end relative.