Running Totals: The Overdraft Alert Never Fired, Because the Lowest Balance the Sheet Ever Saw Was the Closing Balance

Fourteen movements on the current account in September. £14,155.90 in across five receipts, £12,642.85 out across nine payments, an opening balance of £4,820.00 and a closing balance of £6,333.05 that reconciles to the bank statement to the penny.

The treasury summary beside it reported the month's lowest balance as £6,333.05. That is also the closing balance, and that coincidence is the entire tell. The alert that is supposed to fire whenever the balance drops under £1,000 never fired.

The account bottomed out at £627.35 on 10 September, the morning payroll cleared.

Nothing on this sheet is a wrong number. Every amount is right. The closing balance is right. Total in and total out both agree with the statement, and so does the net movement. What happened is that somebody sorted the ledger by amount to find the big items — an entirely reasonable thing to do — and a running balance is the one column on a spreadsheet whose every single value depends on the order of the rows above it.

What this covers. SUM, SUMIF, SUMIFS, SUBTOTAL, MIN, MAX, COUNT, COUNTA, COUNTIF, INDEX, MATCH, ROUND and IF work in every version this century. SCAN, LAMBDA, LET and spilled arrays need Microsoft 365 or Excel 2021, and section 8 says exactly which lines need them. A bank balance is only the example here: stock on hand, holiday days taken, cumulative hours against a budget and year-to-date revenue are all the same column with a different unit, and every trap below applies unchanged.

1) Fourteen Movements and the Two Columns That Agree

The ledger is in A1:D15 — date in A, bank reference in B, category in C, and a signed amount in D, receipts positive and payments negative. The opening balance of 4820.00 sits in F1, outside the table, and the running balance will be built in G2:G15.

Signed is the first decision and it is not a small one. A ledger with separate "in" and "out" columns needs =SUM($D$2:D2)-SUM($E$2:E2) for its running balance and every construction in this article doubles in length; one signed column costs a minus sign at import and pays for itself in every formula afterwards.

Here is the month, with the running balance the date order produces:

Two numbers here are worth writing on a sticky note before you build anything, because every check in this article is one of them:

=SUM($D$2:$D$15)          → 1,513.05     the net movement
=$F$1+SUM($D$2:$D$15)     → 6,333.05     the closing balance

🎯 Scenario: Compute those two cells before you write a running balance, not after. A running balance is a construction with fourteen chances to be wrong and one number at the bottom that can be checked in a single cell; if you only ever look at the bottom, you have built a column of fourteen values and verified one of them.

2) The Chain, and What a Sort Does to It

This is what almost everybody types first, and it is what was on this sheet:

G2:  =$F$1+D2        → 7,970.00
G3:  =G2+D3          → 5,995.00      and drag down

Previous balance plus this movement. It is how a paper ledger works, it reads perfectly, it is fast, and in date order it is completely correct — the column above is exactly the column in section 1.

Then somebody sorts A1:G15 by Amount, largest first, to see where the money went.

Excel sorts every column together, formulas included, and the formula in each row still says "the cell above me, plus my own amount". So the column recomputes without a single error, and it recomputes into the balance history of a month that never happened:

Read the last column. All three close at 6,333.05. Addition is commutative, so whatever order you add fourteen numbers in, the total is the same — which means every reconciliation the finance team runs passes, in all three versions, every month.

And read the middle column. Sorted high-to-low, all the money arrives before it leaves, so the balance climbs to 18,975.90 and only comes down at the end; the lowest value in the column is the last one, which is the closing balance. Sorted low-to-high, everything leaves before any of it arrives, and the sheet reports an account £7,822.85 overdrawn that was never overdrawn at all.

The alert is =IF(MIN($G$2:$G$15)<1000,"OVERDRAFT RISK",""). Against 6,333.05 it is silent. Against -7,822.85 it screams every month until somebody switches it off for crying wolf. Neither version is reporting anything about the account.

🎯 Scenario: A chained running total is not wrong, it is ordered — it is only meaningful while the rows are in the sequence the events happened in. Put the sort key next to it: if the column beside your running balance is a date and that date is not ascending, the running balance is a decoration. Section 11 makes that a one-cell check.

3) The Expanding Range

The construction that does not care about any of this re-derives the balance from scratch on every row:

G2:  =$F$1+SUM($D$2:D2)      → 7,970.00      and drag down
G15: =$F$1+SUM($D$2:D15)     → 6,333.05

One anchored end, one relative end. As the formula is dragged the start of the range stays welded to D2 and the end follows the row, so row 8 sums D2:D8 and row 15 sums D2:D15. Same fourteen numbers as the chain, in date order.

The difference is what happens to it afterwards:

• Sort the table. Every row re-sums the rows now above it, which is exactly what a running total means. The column follows the new order honestly, and if the new order is by amount you get an honest cumulative-by-size curve rather than a corrupted balance history. It answers a different question, but it answers one.
• Insert a row at 8. $D$2:D8 on the rows below it stretches to include the new row automatically. The chain leaves G8 blank, and =G8+D9 reads that blank as zero, so every row from 9 down loses the entire balance accumulated above — the payroll row reads -6,240.00 instead of 627.35, a drop of 6,867.35 appearing out of nowhere, with no error anywhere.
• Delete row 2. The anchor $D$2 becomes #REF! in all fourteen formulas at once. That is the loudest failure in this article, and it is the good kind: it tells you.
• Filter the table. Neither construction cares — see section 5, which is a longer story.

The trade-off is real and section 10 has the numbers: the chain does one addition per row and the expanding range does one addition per row per row above it. On fourteen rows that is nothing. On fifty thousand it is not.

🎯 Scenario: Use =$F$1+SUM($D$2:D2) as the default and reach for the chain only when a recalculation timer tells you to. The default should be the shape that cannot be quietly broken by an ordinary act like sorting a table, because sorting a table is not an act anyone announces.

4) What Half an Anchor Costs

Every wrong version of this formula is a wrong number of dollar signs, and each one fails differently:

=$F$1+SUM($D$2:D2)      correct        7,970.00 … 6,333.05
=$F$1+SUM(D2:D2)        no anchor      7,970.00, 2,845.00 … 6,725.40
=$F$1+SUM($D$2:$D$2)    both anchored  7,970.00 on all fourteen rows
=F1+SUM($D$2:D2)        loose opening  7,970.00, 1,175.00 … 1,513.05

The second one collapses to =$F$1+D2 — the running column becomes the amount column with 4,820.00 added to each row, and because it moves up and down with the amounts it looks alive. The third is a flat column of one repeated value, which nobody misses for long.

The fourth is the one that costs a day. F1 unanchored becomes F2, F3, F4 as it is dragged, and F2 downwards is empty, so from row 3 on the opening balance is silently added as zero. Row 2 is correct. Every row below it is short by exactly 4,820.00, and the closing balance reads 1,513.05 instead of 6,333.05.

That failure is dangerous precisely because 4,820.00 looks like a transaction. It is a round-ish number of a plausible size, and the reconciliation meeting spends its afternoon hunting a missing payment for £4,820.00 that does not exist, in a ledger where the true discrepancy is a dollar sign.

Worth naming the near-miss too: this ledger contains a receipt of exactly 3,150.00 on the 1st and a VAT payment of exactly -3,150.00 on the 18th. Any discrepancy of 3,150.00 has two candidate explanations, and neither of them is the real one when a formula is at fault.

🎯 Scenario: When a running balance is out by a constant on every row after the first, look at the seed cell before you look at the data. A constant offset is an anchoring bug or a wrong opening balance; a growing offset is a range problem; a discrepancy that appears once and persists is a single bad row. Those three shapes are diagnosable from the difference column alone, and the difference column is =G2-($F$1+SUM($D$2:D2)) beside whatever you inherited.

5) The Running Total the Filter Ignores

Filter the ledger to Receipts only. Rows 2, 5, 9, 11 and 15 stay on screen; nine rows disappear.

SUM does not know anything happened. Every running balance in column G still includes the hidden payments, so the visible rows read 7,970.00, 7,329.10, 6,227.35, 8,063.50 and 6,333.05 — five numbers that are correct balances of an account, sitting beside five receipts, in a view labelled Receipts. There is no error, no red, no gap. The column is simply answering a question nobody on screen is asking.

SUBTOTAL with function number 109 sums the visible rows only, and it takes an expanding range exactly like SUM does:

=SUBTOTAL(109,$D$2:D2)      drag down G2:G15

Unfiltered it is the running movement — 3,150.00, 1,175.00, 1,088.60 … 1,513.05. Filtered to Receipts it becomes 3,150.00, 4,570.50, 10,170.50, 12,250.50, 14,155.90: the running total of what you can see, ending on the £14,155.90 of receipts from the opening paragraph.

Three things about that 109:

• 109 excludes manually hidden rows; 9 does not. SUBTOTAL(9,…) follows the filter but still counts rows you hid by right-clicking. If you never hide rows by hand the two behave identically, which is exactly why the difference surfaces on the one day somebody did.
• SUBTOTAL ignores other SUBTOTALs in its range. That is what stops a running SUBTOTAL column from feeding on itself, and it is the reason this idiom works at all.
• It follows the filter, not the sort. A filtered and sorted table gives you a running total of the visible rows in whatever order they are now in, with all of section 2's problems intact.

Where the answer must not move when someone filters — a balance for the bank, an audit figure — that is an argument for SUM, not against it. Pick the one that matches the question and write which you picked in the header: "Balance (all rows)" and "Running total (visible)" are two different columns and can happily coexist.

🎯 Scenario: Any table with a filter arrow on it and a cumulative column needs one of these two decided deliberately. SUM under a filter is not an error condition Excel will ever mention, and a report that changes meaning when a user clicks a filter arrow is a report that will be screenshotted in the wrong state.

6) A Running Total Per Category

The next question is always cumulative within something — spend to date per category, units per product, hours per project. SUMIFS does it with the same date column doing two jobs:

=SUMIFS($D$2:$D$15,$C$2:$C$15,C2,$A$2:$A$15,"<="&A2)

Read it as: sum the amounts where the category matches this row's category and the date is on or before this row's date. Dragged down H2:H15 it gives each row its own category's spend to date:

The five finals add to 1,513.05, the net movement from section 1, which is the check that the category column has no typos and no stray sixth value hiding in it. Note also that the Receipts row of this table is character-for-character the filtered SUBTOTAL column from section 5 — two completely different mechanisms landing on the same five numbers is about as much confirmation as a spreadsheet ever offers.

Two things to get right:

"<="&A2 and not "<=A2. The second is the literal text "<=A2", which matches nothing and returns 0.00 down the whole column. A column of zeros reads as "this category had no spend yet", not as a broken formula, and that is why this typo survives review.

Ties on the date double-count. Every date in this ledger is unique, deliberately. Move the 17 September payment onto 16 September so two Overheads rows share a date, and both of them return the total including both: the column reads -2,436.75, -2,922.60, -2,922.60, and the repeated pair looks exactly like a duplicated row. If your dates repeat, break the tie on something that does not: a row number helper, or a timestamp, giving =SUMIFS($D$2:$D$15,$C$2:$C$15,C2,$E$2:$E$15,"<="&E2) against a sequence column E. That is the same fix as ranking with ties, and it is the reason ledgers ship with a line number.

🎯 Scenario: Whenever you build a cumulative-within-group column, total the group finals and compare to the grand total in one cell. Here that is =SUM(the five finals)-SUM($D$2:$D$15) → 0.00. A category spelt "Overhead" on one row falls out of every group and out of that check simultaneously, which is exactly the point.

7) The Running Total That Resets

Year-to-date, month-to-date and quarter-to-date are running totals with a floor. The floor is the start of the period the row belongs to:

month to date:
=SUMIFS($D$2:$D$15,$A$2:$A$15,">="&DATE(YEAR(A2),MONTH(A2),1),$A$2:$A$15,"<="&A2)

year to date:
=SUMIFS($D$2:$D$15,$A$2:$A$15,">="&DATE(YEAR(A2),1,1),$A$2:$A$15,"<="&A2)

Both take the row's own date apart and rebuild the first day of its period with DATE, so no hard-coded boundary is ever typed and the formula is identical on every row of every year.

This ledger is one month, so both return the same fourteen numbers: 3,150.00, 1,175.00, 1,088.60, 2,509.10, 2,196.35, 2,047.35, -4,192.65, 1,407.35, 1,163.50, 3,243.50, 2,824.90, 2,757.65, -392.35, 1,513.05.

That is not a wasted exercise — it is how you test a reset. Inside a single period, a resetting running total must equal the plain running total minus the opening balance; at the boundary it must drop back to the first row's own amount. Both halves are checkable, the first one here and the second one the moment October's rows land.

Two figures in that sequence are worth pausing on. Month-to-date goes -4,192.65 on 10 September: the month was £4,192.65 in the red on its own movements even though the balance never went below £627.35, because the opening balance was carrying it. And it ends at 1,513.05, not 6,333.05 — a month-to-date column is a movement, a balance column is a position, and reporting one under the other's heading is its own genre of error.

🎯 Scenario: Build the period floor from the row's own date with DATE(YEAR(A2),MONTH(A2),1) rather than pointing at a cell someone types "01/09/2026" into. The typed version is right until the first row of October, and it fails by producing numbers rather than errors — an October row measured from September's floor is a two-month total wearing a month-to-date label.

8) SCAN, LET and the One-Liner

In Microsoft 365, the whole column is one formula in one cell:

=SCAN($F$1,$D$2:$D$15,LAMBDA(running,amount,running+amount))

SCAN takes a starting value, an array, and a function of two arguments; it walks the array carrying the accumulator forward and returns every intermediate value, spilling 7,970.00 down to 6,333.05 into G2:G15. Its sibling REDUCE does the same walk and returns only the last one.

Wrapped in LET so the parts are named:

=LET(
  opening, $F$1,
  amounts, $D$2:$D$15,
  SCAN(opening, amounts, LAMBDA(a,v,a+v))
)

Three honest observations before you rewrite a working sheet:

• It is fast. SCAN walks the array once, so it costs what the chain costs, not what the expanding SUM costs. On large ledgers it is the only construction that is both robust and quick.
• It is one cell. Nobody can overwrite row 9 of it by accident, and nobody can inspect row 9 of it either. A spilled column has no per-row formula to click on and audit, which is a real loss on a ledger somebody else has to sign.
• It does not exist before Microsoft 365. Not in 2019, not in 2021 — LAMBDA, SCAN and REDUCE are 365-only, while LET arrived in 2021. Open the workbook in an older Excel and the cell reads #NAME?. If the file leaves your machine, sections 3 and 5 are the portable answers.

🎯 Scenario: Use SCAN where the workbook is yours, the row count is large and the version is known. Use =$F$1+SUM($D$2:D2) where the workbook will be emailed, audited, or opened in 2016 by a client. This is a distribution decision, not a sophistication contest.

9) The Number a Running Total Exists to Produce

A closing balance does not need a running total — =$F$1+SUM($D$2:$D$15) gives you that in one cell. The reason to build fourteen values is that the interesting facts live in the middle:

=MIN($G$2:$G$15)                                            → 627.35
=MAX($G$2:$G$15)                                            → 8,063.50
=INDEX($A$2:$A$15,MATCH(MIN($G$2:$G$15),$G$2:$G$15,0))      → 10/09/2026
=COUNTIF($G$2:$G$15,"<1000")                                → 1
=COUNTIF($G$2:$G$15,"<0")                                   → 0

The account never went overdrawn, it came within £372.65 of the alert threshold once, and it did so on the day payroll cleared. Those are the three sentences the treasury summary should have contained, and every one of them comes out of the middle of the column — the part that a sort destroys and a total conceals.

The same shape answers the same question in every other unit: the lowest stock level and the day it happened, the peak headcount in a rolling plan, the first date a cumulative spend crosses its budget:

=INDEX($A$2:$A$15,MATCH(TRUE,INDEX($G$2:$G$15<1000,0),0))   → 10/09/2026

MATCH(TRUE, …) finds the first row that breaches rather than the worst one, which is usually the more actionable of the two — the worst day tells you how bad it got, the first day tells you when you could still have done something. The inner INDEX(…,0) is what makes the comparison array work without Ctrl+Shift+Enter in older versions.

🎯 Scenario: If nothing in your workbook reads the middle of the running column — no MIN, no threshold count, no chart — then the column is a fourteen-cell way of computing one number, and =$F$1+SUM($D$2:$D$15) does that with one cell and none of this article's failure modes. Build the column when you need the shape; build the total when you need the total.

10) Speed: n² Against n

Both constructions look the same on screen and cost wildly different amounts to recalculate.

The chain, =G2+D3, does one addition per row. Fourteen rows, fourteen additions; fifty thousand rows, fifty thousand additions.

The expanding range, =$F$1+SUM($D$2:D2), re-adds every row above it. Row 14 sums thirteen cells, row 15 sums fourteen. That is n(n+1)/2 — 105 cell additions here, and at fifty thousand rows 1,250,025,000. On a full-column recalculation that is the difference between imperceptible and a workbook that pauses every time somebody types.

So the honest summary is uncomfortable: the fragile construction is the fast one, and the robust construction is the one that degrades. Three ways out, in the order worth trying them:

1. Leave it. Under a few thousand rows the expanding SUM is free, and most ledgers are under a few thousand rows.
2. Use SCAN. One pass and one cell, robust and linear, at the cost of Microsoft 365 and losing per-row auditability. Section 8 covers the trade.
3. Keep the chain and defend it. If the chain is the only option, add the order check from section 11 in a visible cell and convert the table to an Excel Table so a sort has to be deliberate. The chain is not forbidden; it is a construction with a precondition, and an unstated precondition is what this whole article is about.

🎯 Scenario: Before optimising, check whether the running column is even needed on every row. A cumulative column on 50,000 transaction lines feeding a chart with 24 monthly points can usually be a SUMIFS on 24 rows instead, which is 24 formulas rather than 1.25 billion additions.

11) Four Checks

One: the endpoint. =ROUND($F$1+SUM($D$2:$D$15)-G15,2) must be 0.00. The last running value has to equal the opening balance plus the whole column, and this is the cell that catches the loose-anchor bug from section 4, which lands at 1,513.05 and fails this by exactly 4,820.00.

Two: nothing skipped. =COUNTA($D$2:$D$15)-COUNT($D$2:$D$15) must be 0. Anything else is text where a number should be — "pending", a dash, an amount pasted as text from the bank's CSV — and SUM steps over all of it without a whisper while every total below stays internally consistent.

Three: no gaps. =COUNT($G$2:$G$15) must be 14. A blank in the middle of a chained running column is an inserted row, and =G8+D9 reading that blank as zero is how a balance silently restarts from nothing halfway down a ledger.

Four: the rows are in order. =SUMPRODUCT(--($A$3:$A$15<$A$2:$A$14)) must be 0. This is the check that the whole article exists for: it counts rows whose date is earlier than the row above, which is 0 in date order, 6 sorted high-to-low and 7 sorted low-to-high. It is the only one of the four that fails on the sheet from the opening paragraph, where all three of the others pass.

🎯 Scenario: Put all four above the ledger, not on a hidden tab. Checks one to three test the arithmetic and check four tests the premise, and it is the premise that broke here — a running balance whose numbers all add up correctly in an order that never happened is a document with no errors in it and nothing true in it either.

12) Twelve Traps

1. A chained running total survives sorting and stops meaning anything. The formulas recompute, no error appears, the closing balance stays right, and every intermediate value becomes the balance of a month that did not occur.
2. The closing balance cannot detect the problem. Addition is commutative, so all fourteen orderings of this ledger reconcile to 6,333.05. Any check that only looks at the bottom row passes on all of them.
3. =SUM(D2:D2) is not a running total. With neither end anchored it collapses to the row's own amount, and the column becomes a copy of the amount column that moves convincingly.
4. F1 instead of $F$1 costs the opening balance from row 3 down. Row 2 is correct, every row after is short by a constant, and the constant looks like a missing transaction.
5. Deleting the first data row turns $D$2 into #REF! everywhere at once. This is the failure you want: loud, immediate, and impossible to publish.
6. Inserting a row breaks a chain and heals an expanding range. =G8+D9 reads the new blank G8 as zero and drops everything accumulated above it, with no error on any row.
7. SUM ignores the filter. A filtered view showing five receipts beside five balances that include nine hidden payments is not flagged in any way. SUBTOTAL(109,$D$2:D2) is the version that follows the filter.
8. SUBTOTAL(9,…) and SUBTOTAL(109,…) differ only on manually hidden rows. They agree for months, then disagree on the day somebody right-clicks and hides a row.
9. "<=A2" is literal text. The date criterion has to be built as "<="&A2. The symptom is a column of 0.00, which reads as "nothing spent yet" rather than as a broken formula.
10. Equal dates double-count in a SUMIFS cumulative. Two rows on 16 September both return the total including both, so the sequence repeats a value. Break the tie on a sequence column, not on the date.
11. A running total pasted as values and then re-sorted is unrecoverable and looks perfect. No formula remains to recompute, so the numbers stay put while the rows move around them, and check four in section 11 is the only thing that sees it.
12. The expanding SUM is O(n²). Fine at 1,000 rows, 1.25 billion additions at 50,000. Notice it before the workbook does.

Practice

Using the fourteen rows in the grid above, with the opening balance 4,820.00 in F1:

1. Build both. Put the chain in G and the expanding range in H, confirm they agree on all fourteen rows, then sort the table by Amount descending and say which column still means something. Both will still end on 6,333.05.
2. Find the low point. Write the one formula that returns the lowest balance and the one that returns the date it happened. You should get 627.35 and 10/09/2026.
3. Break it deliberately. Change $F$1 to F1 in the running formula and drag it down. What is the new closing balance, and what is the difference between it and 6,333.05? Why does that difference look like a transaction?
4. Filter-aware. Build =SUBTOTAL(109,$D$2:D2), filter to Receipts, and show that the last visible row reads 14,155.90. Then filter to Overheads and predict the last value before you look.
5. Per category. Build the SUMIFS cumulative from section 6 and prove the five category finals add to 1,513.05. Then rename one "Overheads" to "Overhead" and find the two places that number changes.
6. The order check. Write =SUMPRODUCT(--($A$3:$A$15<$A$2:$A$14)), confirm it is 0, sort by Amount ascending and confirm it is 7. That single cell is the difference between the report in this article's first paragraph and a true one.

Summary

A running total is the only column on a spreadsheet that is a statement about order, and order is the one property of a table that Excel lets anybody change with two clicks and no warning.

So: prefer =$F$1+SUM($D$2:D2) — one absolute end, one relative end — because it re-derives itself from the rows currently above it and cannot be quietly de-synchronised by a sort or an inserted row. Keep the amount signed in one column. Anchor the opening balance, or watch a constant go missing from every row but the first and get investigated as a lost payment. Use SUBTOTAL(109,…) when the column must follow a filter and SUM when it must not, and put which one you chose in the header. Use SUMIFS for cumulative-within-group and for anything that resets on a period, building the period floor from the row's own date. Take SCAN when the workbook is yours and large, and leave it behind when the workbook travels. Then check the endpoint, the gaps, the text, and — the one that actually matters — that the dates are ascending.

The alternative is the version this article opened with: every amount correct, both totals reconciling to the bank, the closing balance right to the penny, an overdraft alert reading the closing balance as the month's low point, and an account that spent a morning £372.65 away from a threshold nobody was told it approached.