Combination
Debtor age analysis table that ties to the control account
Total the open amount per age band with one SUMIFS over a band table, add the bands up with SUM and check that the total agrees with the control account.
Worked example
Age analysis of the receivables at 30 September 2026
Scenario
For Veldhuis Installatie BV you have eight open invoices from the receivables subledger with their due dates. You want the age analysis for the interim audit: the open amount per band (not yet due, 1 to 30, 31 to 60, 61 to 90 and over 90 days) on the report date of 30 September 2026 in F8, and a total that you can tie to the balance of the control account for receivables in F9.
The bands live in a small table, so you can change them without touching the formula.
The data
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Due date | Open amount | Band | From (days) | To (days) | ||
| 2 | 26-0501 | 12 Oct 2026 | €6,400.00€ 6.400,00 | Not yet due | -999-999 | 00 | ||
| 3 | 26-0502 | 2 Oct 2026 | €2,950.00€ 2.950,00 | 1 to 30 days | 11 | 3030 | ||
| 4 | 26-0503 | 20 Sep 2026 | €4,200.00€ 4.200,00 | 31 to 60 days | 3131 | 6060 | ||
| 5 | 26-0504 | 2 Sep 2026 | €7,800.00€ 7.800,00 | 61 to 90 days | 6161 | 9090 | ||
| 6 | 26-0505 | 15 Aug 2026 | €8,500.00€ 8.500,00 | Over 90 days | 9191 | 99999999 | ||
| 7 | 26-0506 | 20 Jul 2026 | €3,250.00€ 3.250,00 | |||||
| 8 | 26-0507 | 10 Jun 2026 | €9,600.00€ 9.600,00 | Report date | 30 Sep 2026 | |||
| 9 | 26-0508 | 25 May 2026 | €2,700.00€ 2.700,00 | Control account balance | 4540045400 | |||
| 10 | Total of the bands | €45,400.00€ 45.400,00=SUM(SUMIFS($C$2:$C$9, $B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6))=SUM(SUMIFS($C$2:$C$9; $B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6)) | ||||||
| 11 |
Swipe sideways to see all columns
Formula in F10
=SUM(SUMIFS($C$2:$C$9,$B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6))
=SUM(SUMIFS($C$2:$C$9;$B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6))
Result: €45,400.00€ 45.400,00
How the pieces fit together
Watch out: An invoice with a blank due date or a due date stored as text matches none of the date criteria, so it drops out of every band and the total ends up below the control account.
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=SUMIFS($C$2:$C$9,
$B$2:$B$9, ">="&$F$8-$G4, $B$2:$B$9, "<="&$F$8-$F4) =SUMIFS($C$2:$C$9;
$B$2:$B$9; ">="&$F$8-$G4; $B$2:$B$9; "<="&$F$8-$F4) SUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Read more about SUMIFSPress Escape to closesum_rangeThe cells whose numbers are added when a row passes every test.Open amount column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Due date column, 8 cellsPress Escape to closecriteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".">=": comparisonsPress Escape to close&Joins textPress Escape to closeReferenceInput cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceInput cell: 60Press Escape to closecriteria_range2 (optional)Range for a further condition; pairs repeat up to 127 times.Due date column, 8 cellsPress Escape to closecriteria2 (optional)The test for criteria_range2, written like criteria1.Press Escape to close&Joins textPress Escape to closeReferenceInput cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceInput cell: 31Press Escape to closeOne band, the one in row 4 (31 to 60 days overdue). The report date 30 September 2026 minus 60 days is 1 August 2026, and minus 31 days is 30 August 2026, so SUMIFS adds the open amounts of invoices due from 1 August to 30 August. Only
26-0505(due 15 August) falls in that window: 8,500.Fill the formula down and every band gets its own total, because the dollar signs fix the invoice list and the report date while the row number follows the band.
=SUMIFS($C$2:$C$9,
$B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6) =SUMIFS($C$2:$C$9;
$B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6) SUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Read more about SUMIFSPress Escape to closesum_rangeThe cells whose numbers are added when a row passes every test.Open amount column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Due date column, 8 cellsPress Escape to closecriteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".">=": comparisonsPress Escape to close&Joins textPress Escape to closeReferenceInput cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceG column, 5 cellsPress Escape to closecriteria_range2 (optional)Range for a further condition; pairs repeat up to 127 times.Due date column, 8 cellsPress Escape to closecriteria2 (optional)The test for criteria_range2, written like criteria1.Press Escape to close&Joins textPress Escape to closeReferenceInput cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceF column, 5 cellsPress Escape to closeGiving the two criteria all five bands at once makes SUMIFS return five totals, one per band, in the order of the band table:
- 9,350 (not yet due)
- 12,000 (1 to 30 days)
- 8,500 (31 to 60 days)
- 3,250 (61 to 90 days)
- 12,300 (over 90 days)
In Excel 365 the five values spill down; this is the finished age analysis.
=SUM(SUMIFS($C$2:$C$9,
$B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6)) =SUM(SUMIFS($C$2:$C$9;
$B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6)) SUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Used here as: number1The first number, cell or range to add.Read more about SUMIFSPress Escape to closesum_rangeThe cells whose numbers are added when a row passes every test.Open amount column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Due date column, 8 cellsPress Escape to closecriteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".">=": comparisonsPress Escape to close&Joins textPress Escape to closeReferenceInput cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceG column, 5 cellsPress Escape to closecriteria_range2 (optional)Range for a further condition; pairs repeat up to 127 times.Due date column, 8 cellsPress Escape to closecriteria2 (optional)The test for criteria_range2, written like criteria1.Press Escape to close&Joins textPress Escape to closeReferenceInput cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceF column, 5 cellsPress Escape to closeSUM adds the five band totals: 9,350 + 12,000 + 8,500 + 3,250 + 12,300 = 45,400. This is the total of the age analysis, the whole formula of the example.
=SUM(SUMIFS($C$2:$C$9,
$B$2:$B$9, ">="&$F$8-$G$2:$G$6, $B$2:$B$9, "<="&$F$8-$F$2:$F$6))-$F$9 =SUM(SUMIFS($C$2:$C$9;
$B$2:$B$9; ">="&$F$8-$G$2:$G$6; $B$2:$B$9; "<="&$F$8-$F$2:$F$6))-$F$9 SUMIFS SOMMEN.ALSAdds the numbers that meet all of several conditions.Used here as: number1The first number, cell or range to add.Read more about SUMIFSPress Escape to closesum_rangeThe cells whose numbers are added when a row passes every test.Open amount column, 8 cellsPress Escape to closecriteria_range1The range the first condition is tested on; same size as sum_range.Due date column, 8 cellsPress Escape to closecriteria1The test: a value, a cell, or text such as ">=100" or "MEM-*".">=": comparisonsPress Escape to close&Joins textPress Escape to closeReferenceInput cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceG column, 5 cellsPress Escape to closecriteria_range2 (optional)Range for a further condition; pairs repeat up to 127 times.Due date column, 8 cellsPress Escape to closecriteria2 (optional)The test for criteria_range2, written like criteria1.Press Escape to close&Joins textPress Escape to closeReferenceInput cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceF column, 5 cellsPress Escape to close-SubtractsPress Escape to closeReferenceInput cell: 45400Press Escape to closeThe check: the total of the bands minus the balance of the control account in
F9(45,400). The difference is 0, so every invoice sits in exactly one band and the analysis agrees with the ledger. Any other number points to an invoice that is in no band or in two.
Step by step
- $F$8-$G$2:$G$6 gives the earliest due date of each band: 30 Sep 2026 minus 0 = 30 Sep 2026, minus 30 = 31 Aug 2026, minus 60 = 1 Aug 2026, minus 90 = 2 Jul 2026 and minus 9,9999.999 = 16 May 1999, a date so far back that no invoice is older.
- $F$8-$F$2:$F$6 gives the latest due date of each band: 30 Sep 2026 minus -999 = 25 Jun 2029 (so no invoice is too far in the future), minus 1 = 29 Sep 2026, minus 31 = 30 Aug 2026, minus 61 = 31 Jul 2026 and minus 91 = 1 Jul 2026.
- SUMIFS adds the open amounts of the invoices whose due date lies between those two dates. Not yet due (30 Sep 2026 to 25 Jun 2029):
26-0501(12 Oct) 6,4006.400 +26-0502(2 Oct) 2,9502.950 = 9,3509.350. 1 to 30 days (31 Aug to 29 Sep):26-0503(20 Sep) 4,2004.200 +26-0504(2 Sep) 7,8007.800 = 12,00012.000. - 31 to 60 days (1 Aug to 30 Aug): only
26-0505(15 Aug) = 8,5008.500. 61 to 90 days (2 Jul to 31 Jul): only26-0506(20 Jul) = 3,2503.250. - Over 90 days (16 May 1999 to 1 Jul 2026):
26-0507(10 Jun) 9,6009.600 +26-0508(25 May) 2,7002.700 = 12,30012.300. - SUM adds the five band totals: 9,3509.350 + 12,00012.000 + 8,5008.500 + 3,2503.250 + 12,30012.300 = 45,40045.400.
- The control account balance in
F9is also 45,40045.400, so the difference is 0: all eight invoices (6,4006.400 + 2,9502.950 + 4,2004.200 + 7,8007.800 + 8,5008.500 + 3,2503.250 + 9,6009.600 + 2,7002.700 = 45,40045.400) are in exactly one band.
Why it works
SUMIFS accepts a range where a single criterion is expected and then returns one total per cell of that range. With the band limits turned into due dates, one formula answers the question for every band, and SUM turns the bands back into a total. Because the bands are in cells, a different band policy needs a new table, not a new formula, and the total can be compared with the ledger to prove that no invoice fell out of the table.
Watch out for
- The check in
F9exposes such an invoice: investigate every difference before you rely on the table. The bands must join without gaps or overlaps: 30 and then 31, not 30 and then 40. A gap loses invoices, an overlap counts them twice, and in both cases the total no longer agrees with the control account.
The outer limits -999 and 9,9999.999 are deliberately wide so that no invoice falls outside the first or last band.
Before Excel 365, SUMIFS with a range as criterion needs Ctrl+Shift+Enter inside SUM, or you fill the one-band formula down the table and add the cells up.
The report date is typed in
F8so the table stays the same when the workpaper is opened later; put TODAY() there only for a live overview.
Variations
=SUMPRODUCT(($F$8-$B$2:$B$9>=$F4)*($F$8-$B$2:$B$9<=$G4)*$C$2:$C$9)
=SUMPRODUCT(($F$8-$B$2:$B$9>=$F4)*($F$8-$B$2:$B$9<=$G4)*$C$2:$C$9)
The same band in older Excel versions without SUMIFS array behavior: SUMPRODUCT tests the days overdue against the band limits in row 4. The due date 15 August is 46 days overdue, 20 July is 72 days and so on; only 46 lies between 31 and 60, so the result is 8,5008.500.
=SUMIFS($C$2:$C$9,$B$2:$B$9, "<"&$F$8)
=SUMIFS($C$2:$C$9;$B$2:$B$9; "<"&$F$8)
The classic shortcut when you only need the overdue part: all invoices due before the report date. 4,2004.200 + 7,8007.800 + 8,5008.500 + 3,2503.250 + 9,6009.600 + 2,7002.700 = 36,05036.050, which is 45,40045.400 minus the 9,3509.350 that is not yet due.
Functions used