October's ageing report showed 5,545.00 more than 60 days overdue. The sales ledger said 11,305.00. One customer the stop list should have caught was not on it, and on 3 November it placed another order on account.
| Halden Timber, aged at 31/10/2026 | Report | Ledger |
|---|---|---|
| Total in the five buckets | 18,160.00 | 38,470.00 |
| More than 60 days overdue | 5,545.00 | 11,305.00 |
| Customers on the stop list | 2 | 3 |
| Debt in no bucket | 20,310.00 | 0.00 |
Scope.
SUMIFS,COUNTIFS,MAX,ROUNDandVLOOKUPwork in every version of Excel since 2010.XLOOKUPneeds Excel 2021 or Microsoft 365, and section 5 gives theVLOOKUPversion for older files.Throughout, the invoices are in A1:F10: customer in B, due date in C, amount in D, days overdue in E and bucket in F. The ageing date is in H2, the band table in H5:I9 and the bucket totals in A12:F18.
01The Buckets Did Not Add Up to the Ledger
Halden Timber sells on 30-day terms, and its credit controller runs an ageing report on the last day of each month. Any customer with debt more than 60 days overdue goes on stop until it pays.
The report totals each bucket with SUMIFS on the days-overdue column. The 61–90 cell, for example, reads:
=SUMIFS($D$2:$D$10,$E$2:$E$10,">60",$E$2:$E$10,"<90")
Nine Invoices, Five Buckets, Three Gaps
Halden Timber's open invoices at 31 October 2026, in A1:F10: due date in C, amount in D, days overdue in E and the bucket from the band table in F. H2 holds the ageing date and H5:I9 the band table of lower bounds. Rows 13 to 18 total the buckets twice: D is the report's SUMIFS with "<" and ">" criteria, E the fixed version, F what the report missed.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
The pack shows the five buckets and no total. Added up, they come to 18,160.00, while the ledger's open invoices in D2:D10 come to 38,470.00. Ashby Lofts owed 5,760.00 at 90 days and appeared in no bucket at all.
Scenario: Put the nine invoices in A2:E10 and enter the five report formulas from section 2 in D13:D17. Enter =SUM(D13:D17) in D18 and =SUM(D2:D10) in D20, and check that they read 18,160.00 and 38,470.00.
02"<" and ">" Leave a Gap at Every Boundary
Each bucket was written with strict comparisons on both sides. A strict "<30" at the top of one bucket and a strict ">30" at the bottom of the next both exclude day 30, so an invoice exactly 30 days overdue belongs to neither.
| Bucket | Report criteria | Days it misses |
|---|---|---|
| Current | "<=0" | none |
| 1–30 | ">0", "<30" | 30 |
| 31–60 | ">30", "<60" | 60 |
| 61–90 | ">60", "<90" | 90 |
| 91+ | ">90" | none |
The three missing days were exactly the three on which invoices sat. INV-2150 at 30 days, INV-2118 at 60 and INV-2084 at 90 add up to 20,310.00, the whole of the gap.
SUMIFS gives no warning. A criterion that matches nothing returns 0, which looks the same as a bucket that is genuinely empty.
Scenario: Enter =COUNTIFS($E$2:$E$10,">0",$E$2:$E$10,"<30") in G14 and check that it reads 1, not 2. Change E5 from 30 to 29 and watch the 1–30 bucket jump by 6,350.00.
03Month-End Ageing Lands Invoices on the Boundaries
A gap of one day sounds rare. At month-end it is not, because due dates cluster on the first of the month and the report runs on the last.
Days overdue is the ageing date minus the due date, floored at zero so invoices not yet due count as current:
=MAX(0,$H$2-C2)
Due 1 October and aged on 31 October is 30 days to the day. Due 1 September is 60. A quarter-end run catches the same pattern at 90, so the report that leaks most is the one the credit committee reads.
| Invoice | Due date | Aged at | Days overdue |
|---|---|---|---|
| INV-2150 | 01/10/2026 | 31/10/2026 | 30 |
| INV-2118 | 01/09/2026 | 31/10/2026 | 60 |
| INV-2084 | 02/08/2026 | 31/10/2026 | 90 |
Scenario: Enter =MAX(0,$H$2-C2) in E2 and fill it down to E10. Check that E5, E7 and E9 read 30, 60 and 90, then change H2 to 30/10/2026 and watch all three drop out of the gaps.
04Close Every Gap: Pair ">" With "<="
The fix is one rule applied to every boundary. Each bucket includes its upper bound and excludes its lower one, so the next bucket starts exactly where the last one stopped.
Current =SUMIFS($D$2:$D$10,$E$2:$E$10,0)
1–30 =SUMIFS($D$2:$D$10,$E$2:$E$10,">0",$E$2:$E$10,"<=30")
31–60 =SUMIFS($D$2:$D$10,$E$2:$E$10,">30",$E$2:$E$10,"<=60")
61–90 =SUMIFS($D$2:$D$10,$E$2:$E$10,">60",$E$2:$E$10,"<=90")
91+ =SUMIFS($D$2:$D$10,$E$2:$E$10,">90")
Read the criteria down the column: every number appears twice, once as "<=" and once as ">". That pairing is the whole test, and you can check it by eye in seconds.
| Bucket | Report | Fixed | Missing |
|---|---|---|---|
| Current | 6,820.00 | 6,820.00 | 0.00 |
| 1–30 | 3,920.00 | 10,270.00 | 6,350.00 |
| 31–60 | 1,875.00 | 10,075.00 | 8,200.00 |
| 61–90 | 2,430.00 | 8,190.00 | 5,760.00 |
| 91+ | 3,115.00 | 3,115.00 | 0.00 |
| Total | 18,160.00 | 38,470.00 | 20,310.00 |
Scenario: Enter =SUMIFS($D$2:$D$10,$E$2:$E$10,">30",$E$2:$E$10,"<=60") in E15 and the other four fixed formulas in E13:E17. Check that E15 reads 10,075.00 and E18 reads 38,470.00.
05Bucket Each Invoice Once With XLOOKUP
Five hand-written criteria pairs are five chances to get a boundary wrong. A band table of lower bounds removes the choice: each invoice takes the last band whose lower bound it has reached.
| From | Bucket |
|---|---|
| 0 | Current |
| 1 | 1–30 |
| 31 | 31–60 |
| 61 | 61–90 |
| 91 | 91+ |
With the table in H5:I9, the bucket for row 2 is:
=XLOOKUP(E2,$H$5:$H$9,$I$5:$I$9,,-1)
The -1 asks for an exact match or the next smaller value. Day 30 finds no 30, falls back to 1 and reads 1–30; day 31 matches 31 exactly. There is no value between two bounds for an invoice to fall through.
The bucket totals then match on the label, not on the days, so they cannot leak:
=SUMIFS($D$2:$D$10,$F$2:$F$10,A13)
Older Excel.
=VLOOKUP(E2,$H$5:$I$9,2,TRUE)does the same job in every version. It needs the lower bounds sorted smallest first, which a band table should be anyway.Changing the bands is now an edit to the table. A 1–15 and 16–30 split is one new row, with no formula rewritten.
Scenario: Build the band table in H5:I9 and enter =XLOOKUP(E2,$H$5:$H$9,$I$5:$I$9,,-1) in F2, filled down to F10. Check that F5 reads 1–30 and F9 reads 61–90.
06Prove It: Buckets Must Equal the Ledger
An ageing report is a split of the ledger, so the pieces must add back to the whole. Put the check beside the totals and let the sheet fail loudly:
=ROUND(SUM(E13:E17)-SUM(D2:D10),2)=0
TRUE means every invoice landed in a bucket. The ROUND stops a binary rounding difference in pence from turning a correct report FALSE.
Count as well as sum. An invoice whose days are blank or text gets #N/A in column F and drops out of every bucket, and a zero-value one moves no total. The count catches both:
=SUMPRODUCT(COUNTIFS($F$2:$F$10,A13:A17))=COUNTA(A2:A10)
On the report's original formulas, the first check returns FALSE and shows 20,310.00 missing. It would have stopped the pack before it went out.
Scenario: Enter =ROUND(SUM(D13:D17)-SUM(D2:D10),2)=0 in D19 and the same with column E in E19. Check that D19 reads FALSE and E19 reads TRUE.
07Age From the Due Date, on a Fixed Date
Two more choices move invoices between buckets. Age from the due date, not the invoice date, because a customer on 30-day terms is not late on day 1.
| INV-2084, Ashby Lofts | Date | Days at 31/10/2026 | Bucket |
|---|---|---|---|
| Invoice date | 03/07/2026 | 120 | 91+ |
| Due date | 02/08/2026 | 90 | 61–90 |
Some firms age from the invoice date on purpose, and both are legitimate. What is not legitimate is mixing them, so write the basis in the report's title.
Then type the ageing date into H2 rather than using TODAY(). TODAY() recalculates every time the file opens, so the saved October report says something different the next morning.
A fixed date also makes the report reproducible. Change H2 to 30/09/2026 and the same sheet shows September's ageing, as long as the invoice list has not been cleared of what was paid since.
Scenario: Add the invoice date 03/07/2026 in J9 and enter =MAX(0,$H$2-J9) in K9. Check that it reads 120, against 90 in E9.
08Build the Stop List From the Same Days
The stop rule is "more than 60 days overdue", and it must read the same days column with the same boundary as the buckets, or the two will disagree.
=SUMIFS($D$2:$D$10,$B$2:$B$10,A22,$E$2:$E$10,">60")
With customer names in A22:A26, a balance above zero means stop. Fixed, three customers qualify for 11,305.00; the report's buckets had shown two and 5,545.00.
| Customer | Over 60 days | Stop |
|---|---|---|
| Tolland Roofing | 2,430.00 | Yes |
| Ashby Lofts | 5,760.00 | Yes |
| Marlow Interiors | 3,115.00 | Yes |
| Oakmere Homes | 0.00 | No |
| Brackley Build | 0.00 | No |
Oakmere's 8,200.00 at exactly 60 days is not over 60, so it stays off. That is the rule as written, and the sheet now applies it at every boundary the same way.
On Microsoft 365,
=UNIQUE(FILTER(B2:B10,E2:E10>60))spills the stop list straight from the invoices, with no customer list to maintain.
Scenario: List the five customers in A22:A26 and enter =SUMIFS($D$2:$D$10,$B$2:$B$10,A22,$E$2:$E$10,">60") in B22, filled down. Check that Ashby Lofts reads 5,760.00.
09Five Traps
- Strict comparisons on both sides.
"<30"and">30"both exclude day 30. One side of every boundary must be inclusive. - No total on the report. Five buckets with no total hide a gap; the ledger check in section 6 catches it.
- Negative days overdue. Without
MAX(0,…), invoices not yet due show as −15 and fall below a0criterion. - Ageing from the invoice date by accident. INV-2084 reads 120 days instead of 90 and jumps a bucket.
TODAY()in a saved report. The October pack changes its figures every time it is opened.
10Four Exercises
- Reproduce it. Build the report's five
"<"and">"formulas and confirm they total 18,160.00. - Fix it. Pair
">"with"<="and confirm the buckets total 38,470.00. - Replace it. Bucket each invoice with the XLOOKUP band table and total by label.
- Prove it. Add the ledger check and confirm it reads FALSE on the report and TRUE on the fix.
What to Take Away
Every invoice on Halden's ledger was right, and so was every days-overdue figure. Three boundaries written as "less than" on both sides dropped 20,310.00 out of the report and one customer off the stop list.
Write each boundary once, inclusive on one side, ideally in a band table. Then make the buckets add back to the ledger, so a gap can never again go out in the pack.