Combination
Receivables aging total per 30-day band
Turn every due date into a band with XLOOKUP and match_mode -1 on a band table, then let SUM add up the open amounts of one band.
Functions used:XLOOKUPSUMExcel 2021 or later (This combination needs Excel 2021 or later)
Worked example
Open receivables 31 to 60 days overdue
Scenario
For the receivables of Veldhuis Installatie BV you have the open items from the client's aging list with their due dates. You want to know how much is 31 to 60 days overdue on the report date, 30 September 2026 (typed in H3), to judge the allowance for doubtful debts.
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 | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Due date | Open amount | Band from (days) | Chosen band | 3131 | |||
| 2 | 26-0301 | 15 Sep 2026 | €4,200.00€ 4.200,00 | 11 | Total | €14,700.00€ 14.700,00=SUM((XLOOKUP($H$3-B2:B8, E2:E5, E2:E5, 0, -1)=H1)*C2:C8)=SUM((XLOOKUP($H$3-B2:B8; E2:E5; E2:E5; 0; -1)=H1)*C2:C8) | |||
| 3 | 26-0302 | 20 Aug 2026 | €8,500.00€ 8.500,00 | 3131 | Report date | 30 Sep 2026 | |||
| 4 | 26-0303 | 5 Aug 2026 | €3,250.00€ 3.250,00 | 6161 | |||||
| 5 | 26-0304 | 10 Oct 2026 | €6,000.00€ 6.000,00 | 9191 | |||||
| 6 | 26-0305 | 15 Jul 2026 | €12,400.00€ 12.400,00 | ||||||
| 7 | 26-0306 | 20 Jun 2026 | €5,800.00€ 5.800,00 | ||||||
| 8 | 26-0307 | 30 Aug 2026 | €2,950.00€ 2.950,00 | ||||||
| 9 |
Swipe sideways to see all columns
Formula in H2
=SUM((XLOOKUP($H$3-B2:B8,E2:E5, E2:E5, 0, -1)=H1)*C2:C8)
=SUM((XLOOKUP($H$3-B2:B8;E2:E5; E2:E5; 0; -1)=H1)*C2:C8)
Result: €14,700.00€ 14.700,00
How the pieces fit together
Watch out: Without the 0 as the fourth argument, an invoice that is not yet due returns #N/A and SUM returns #N/A for the whole total.
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=$H$3-B2:B8
=$H$3-B2:B8
ReferenceInput cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceDue date column, 7 cellsPress Escape to closeDays overdue per invoice, with the report date in
H3being 30 September 2026. The result is one column of seven values: 15, 41, 56, -10, 77, 102 and 31. The negative value is an invoice that is not yet due.=XLOOKUP($H$3-B2:B8,
E2:E5, E2:E5, 0, -1) =XLOOKUP($H$3-B2:B8;
E2:E5; E2:E5; 0; -1) XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Read more about XLOOKUPPress Escape to closelookup_valueThe value you are looking for.Input cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceDue date column, 7 cellsPress Escape to closelookup_arrayThe one column or row to search in.E column, 4 cellsPress Escape to closereturn_arrayThe range the answer comes from.E column, 4 cellsPress Escape to closeif_not_found (optional)What to show when nothing matches.Press Escape to closematch_mode (optional)Exact, next smaller, next larger, wildcard or regex.-1: exact match, or the next smaller valuePress Escape to closeMatch mode -1 returns the largest band start that is smaller than or equal to the days overdue. The seven values become 1, 31, 31, 0, 61, 91 and 31. The -10 finds no band start and gets the 0 from the fourth argument, which stands for not yet due.
=XLOOKUP($H$3-B2:B8,
E2:E5, E2:E5, 0, -1)=H1 =XLOOKUP($H$3-B2:B8;
E2:E5; E2:E5; 0; -1)=H1 XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Read more about XLOOKUPPress Escape to closelookup_valueThe value you are looking for.Input cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceDue date column, 7 cellsPress Escape to closelookup_arrayThe one column or row to search in.E column, 4 cellsPress Escape to closereturn_arrayThe range the answer comes from.E column, 4 cellsPress Escape to closeif_not_found (optional)What to show when nothing matches.Press Escape to closematch_mode (optional)Exact, next smaller, next larger, wildcard or regex.-1: exact match, or the next smaller valuePress Escape to close=Equal toPress Escape to closeReferenceInput cell: 31Press Escape to closeCompares each band with the chosen band 31 in
H1. The result is FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, TRUE: invoices26-0302,26-0303and26-0307are 31 to 60 days overdue.=(XLOOKUP($H$3-B2:B8,
E2:E5, E2:E5, 0, -1)=H1)*C2:C8 =(XLOOKUP($H$3-B2:B8;
E2:E5; E2:E5; 0; -1)=H1)*C2:C8 XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Read more about XLOOKUPPress Escape to closelookup_valueThe value you are looking for.Input cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceDue date column, 7 cellsPress Escape to closelookup_arrayThe one column or row to search in.E column, 4 cellsPress Escape to closereturn_arrayThe range the answer comes from.E column, 4 cellsPress Escape to closeif_not_found (optional)What to show when nothing matches.Press Escape to closematch_mode (optional)Exact, next smaller, next larger, wildcard or regex.-1: exact match, or the next smaller valuePress Escape to close=Equal toPress Escape to closeReferenceInput cell: 31Press Escape to close*MultipliesPress Escape to closeReferenceOpen amount column, 7 cellsPress Escape to closeMultiplying by the open amounts keeps the amount where the band matches and gives 0 elsewhere: 0, 8,500, 3,250, 0, 0, 0 and 2,950.
=SUM((XLOOKUP($H$3-B2:B8,
E2:E5, E2:E5, 0, -1)=H1)*C2:C8) =SUM((XLOOKUP($H$3-B2:B8;
E2:E5; E2:E5; 0; -1)=H1)*C2:C8) XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Used here as: number1The first number, cell or range to add.Read more about XLOOKUPPress Escape to closelookup_valueThe value you are looking for.Input cell: 30 Sep 2026Press Escape to close-SubtractsPress Escape to closeReferenceDue date column, 7 cellsPress Escape to closelookup_arrayThe one column or row to search in.E column, 4 cellsPress Escape to closereturn_arrayThe range the answer comes from.E column, 4 cellsPress Escape to closeif_not_found (optional)What to show when nothing matches.Press Escape to closematch_mode (optional)Exact, next smaller, next larger, wildcard or regex.-1: exact match, or the next smaller valuePress Escape to close=Equal toPress Escape to closeReferenceInput cell: 31Press Escape to close*MultipliesPress Escape to closeReferenceOpen amount column, 7 cellsPress Escape to closeSUM adds the seven products: 8,500 + 3,250 + 2,950 = 14,700, the open amount in the band 31 to 60 days.
Step by step
- $H$3-B2:B8 gives the days overdue, with the report date 30 Sep in
H3: 30 Sep minus 15 Sep = 15, minus 20 Aug = 41, minus 5 Aug = 56, minus 10 Oct = -10, minus 15 Jul = 77, minus 20 Jun = 102, minus 30 Aug = 31. - XLOOKUP looks each value up in the band starts 1, 31, 61 and 91 with match_mode -1, the largest start that is not above the value: 15 gives 1, 41 gives 31, 56 gives 31, 77 gives 61, 102 gives 91 and 31 gives 31 (an exact match).
- The value -10 is below the first band start, so nothing is found and the fourth argument returns 0. The seven bands are 1, 31, 31, 0, 61, 91, 31.
- Comparing with
H1(31) gives FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, TRUE. - Multiplying by the amounts gives 0, 8,5008.500, 3,2503.250, 0, 0, 0 and 2,9502.950, because TRUE counts as 1 and FALSE as 0.
- SUM adds them: 8,5008.500 + 3,2503.250 + 2,9502.950 = 14,70014.700.
Why it works
Match mode -1 turns a table of band starts into a ruler: every day count lands in the band whose start is closest below it. The comparison with the chosen band selects the invoices, and SUM adds only those amounts. Because the bands are in cells, a different band policy needs a new table, not a new formula.
Watch out for
- The order of the band starts does not change the result, because XLOOKUP with match_mode -1 checks every row, but sorted bands are simpler to read.
- The report date is typed in
H3, so the total does not change when the workpaper is opened later. Put TODAY() inH3only for a live overview: then the days overdue, and the total, shift every day. - If a due date is stored as text, the subtraction fails or gives wrong days. Check that column B is real dates, for example by changing the format to a number.
Variations
=SUMIFS(C2:C8,B2:B8, "<="&$H$3-31, B2:B8, ">"&$H$3-61)
=SUMIFS(C2:C8;B2:B8; "<="&$H$3-31; B2:B8; ">"&$H$3-61)
The classic alternative: SUMIFS with two date criteria for one fixed band. Due dates up to 30 Aug and after 31 Jul give 14,70014.700 as well, but the band is written into the formula.
=SUMPRODUCT(($H$3-B2:B8>=31)*($H$3-B2:B8<=60)*C2:C8)
=SUMPRODUCT(($H$3-B2:B8>=31)*($H$3-B2:B8<=60)*C2:C8)
SUMPRODUCT with two day conditions: 41, 56 and 31 qualify, so 8,5008.500 + 3,2503.250 + 2,9502.950 = 14,70014.700. It works in older Excel versions, but you have to type the limits of the band yourself.
Related combinations
Functions used