01 / 11The Buckets Did Not Add Up to the Ledger

Credit ControlSUMIFSXLOOKUP

Accounts Receivable Aging Report in Excel With SUMIFS

The Ageing Report Lost 20,310.00 of Debt Between Its Buckets and Ashby Lofts Kept Ordering at 90 Days Overdue, Because Every Boundary Said Less Than

5 Nov 202610 min read

UsesSUMIFSXLOOKUPCOUNTIFSMIN, MAX

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/2026ReportLedger
Total in the five buckets18,160.0038,470.00
More than 60 days overdue5,545.0011,305.00
Customers on the stop list23
Debt in no bucket20,310.000.00

Scope. SUMIFS, COUNTIFS, MAX, ROUND and VLOOKUP work in every version of Excel since 2010. XLOOKUP needs Excel 2021 or Microsoft 365, and section 5 gives the VLOOKUP version 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.

ABCDEFGHI
1
Invoice
Customer
Due date
Amount
Days overdue
Bucket
Aged at
2
INV-2201
Brackley Build
15/11/2026
4180
0
Current
31/10/2026
3
INV-2187
Oakmere Homes
31/10/2026
2640
0
Current
4
INV-2163
Tolland Roofing
18/10/2026
3920
13
1–30
5
INV-2150
Brackley Build
01/10/2026
6350
30
1–30
From
Bucket
6
INV-2131
Marlow Interiors
22/09/2026
1875
39
31–60
0
Current
7
INV-2118
Oakmere Homes
01/09/2026
8200
60
31–60
1
1–30
8
INV-2096
Tolland Roofing
14/08/2026
2430
78
61–90
31
31–60
9
INV-2084
Ashby Lofts
02/08/2026
5760
90
61–90
61
61–90
10
INV-2052
Marlow Interiors
20/06/2026
3115
133
91+
91
91+
11
12
Bucket
Report
Fixed
Missing
13
Current
6820
6820
0
14
1–30
3920
10270
6350
15
31–60
1875
10075
8200
16
61–90
2430
8190
5760
17
91+
3115
3115
0
18
Total
18160
38470
20310

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.

Try it in the grid

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.

BucketReport criteriaDays 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.

Try it in the grid

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.

InvoiceDue dateAged atDays overdue
INV-215001/10/202631/10/202630
INV-211801/09/202631/10/202660
INV-208402/08/202631/10/202690

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.

Try it in the grid

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.

BucketReportFixedMissing
Current6,820.006,820.000.00
1–303,920.0010,270.006,350.00
31–601,875.0010,075.008,200.00
61–902,430.008,190.005,760.00
91+3,115.003,115.000.00
Total18,160.0038,470.0020,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.

Try it in the grid

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.

FromBucket
0Current
11–30
3131–60
6161–90
9191+

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.

Try it in the grid

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.

Try it in the grid

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 LoftsDateDays at 31/10/2026Bucket
Invoice date03/07/202612091+
Due date02/08/20269061–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.

Try it in the grid

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.

CustomerOver 60 daysStop
Tolland Roofing2,430.00Yes
Ashby Lofts5,760.00Yes
Marlow Interiors3,115.00Yes
Oakmere Homes0.00No
Brackley Build0.00No

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.

Try it in the grid

09Five Traps

  1. Strict comparisons on both sides. "<30" and ">30" both exclude day 30. One side of every boundary must be inclusive.
  2. No total on the report. Five buckets with no total hide a gap; the ledger check in section 6 catches it.
  3. Negative days overdue. Without MAX(0,…), invoices not yet due show as −15 and fall below a 0 criterion.
  4. Ageing from the invoice date by accident. INV-2084 reads 120 days instead of 90 and jumps a bucket.
  5. TODAY() in a saved report. The October pack changes its figures every time it is opened.

10Four Exercises

  1. Reproduce it. Build the report's five "<" and ">" formulas and confirm they total 18,160.00.
  2. Fix it. Pair ">" with "<=" and confirm the buckets total 38,470.00.
  3. Replace it. Bucket each invoice with the XLOOKUP band table and total by label.
  4. 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.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge