What You Will Learn
Five days of sales in B2:B6: 1200, 850, 2100, 1500, 3000. You need a running total in C2:C6 that grows with each row — row 2 shows 1200, row 3 shows 2050 (1200+850), row 4 shows 4150, and so on.
The formula uses an expanding range. The start stays locked, the end moves down:
=SUM($B$2:B2)
The dollar sign on $B$2 locks the start. The second part B2 is relative, so when you fill down it becomes B3, B4, and so on. That is called a mixed reference — absolute start, relative end. Each row sums everything from the first sale down to its own row.
Each row sums from B2 down to its own row. The range expands as it goes.
The audit formulas:
- B9:
=COUNTA(A2:A6) — counts transaction days.
- B10:
=SUM(B2:B6) — the final total. Should match the last running total value in C6 (8650). If they do not match, something is wrong in the running total chain.
Alternative: Chain Addition
Another way to build a running total is to reference the previous row's total and add the current value:
=C1+B2
This is simpler to read but fragile — if any row's formula gets deleted or overwritten, every row below it breaks. The expanding SUM pattern is safer for that reason.
Alternative: SUBTOTAL for Filtered Views
If your sheet uses filters and you want the running total to react to visible rows only, replace SUM with SUBTOTAL:
=SUBTOTAL(9,$B$2:B2)
SUBTOTAL ignores rows hidden by filters, so it is useful in dashboards where the running total should adjust based on what the viewer is looking at.
The most common mistake here is forgetting the dollar sign on $B$2. Without it, the range shifts down as you fill and the running total stops being cumulative — it just shows each individual row.
Another detail: the first row of the running total has no previous value to build from, which is why the expanding SUM pattern is safer than the chain-add approach (=C1+B2). That chain pattern needs a starting value in the row above the data, or the first cell returns an error. SUM with a mixed reference works cleanly from the first row because it only sums what exists.
Use SUM($B$2:B2) in C2:C6 to create the running total. Then use COUNTA in B9 for transaction count and SUM in B10 for final total. Do not forget the dollar sign!