Financial
Subtotal open invoices by invoice-age brackets with SUMIFS
Aggregate open invoice balances into standard 30-day AR aging subtotal buckets using elapsed age DaysOld = AuditDate - InvoiceDate and SUMIFS.
Turn raw open invoice dates into an auditable 30/60/90-day AR subtotal summary!
This example groups signed open balances by calendar days since invoice date, not days overdue. Due-date aging needs a due-date column and payment terms; a 0–30-day-old invoice is not automatically current or overdue. Use a fixed statement date and open balances from that same snapshot.
The four buckets are 0–30,31–60,61–90 and91+ days. The example validates whole numeric date serials on or before the statement date, so future dates cannot silently fall into the youngest bucket or disappear from reconciliation. Negative balances may represent credits; totals are signed balances rather than gross exposures.
| Invoice (A) | Invoice Date (B) | Balance (C) | Days Old vs 2026-10-30 (D) |
|---|---|---|---|
| INV-501 | 2026-10-15 | $4,200 | 15 days (0–30 age bucket) |
| INV-502 | 2026-09-15 | $3,100 | 45 days (31–60 Bucket) |
| INV-503 | 2026-09-01 | $1,900 | 59 days (31–60 Bucket -> $5,000) |
| INV-504 | 2026-08-15 | $2,600 | 76 days (61–90 Bucket) |
| INV-505 | 2026-06-20 | $5,400 | 132 days (Over 90 -> Total = $17,200) |
Subtotal two-sided aging bucket (e.g., 31 to 60 days old)
Use ">=" on the lower bound and "<=" on the upper bound so boundary days 30, 31, 60, and 61 are never double-counted or dropped!
=IF(OR(COUNT(B2:C6)<>10,NOT(ISNUMBER(F2))),"Invalid inputs",IF(OR(COUNT(B2:C6)<>10,NOT(ISNUMBER(F2)),F2<>INT(F2),MIN(B2:B6)<0,MAX(B2:B6)>F2,B2<>INT(B2),B3<>INT(B3),B4<>INT(B4),B5<>INT(B5),B6<>INT(B6)),"Invalid inputs",SUMIFS(C2:C6, D2:D6, ">=31", D2:D6, "<=60")))Subtotal open-ended Over-90-Days bucket & reconciliation check
Changing the statement date reclassifies these fixed balances. It does not reconstruct historical payments or produce a new real receivables snapshot. The sum of all four bucket formulas must equal the signed balance total after validation.
=IF(OR(COUNT(B2:C6)<>10,NOT(ISNUMBER(F2))),"Invalid inputs",IF(OR(COUNT(B2:C6)<>10,NOT(ISNUMBER(F2)),F2<>INT(F2),MIN(B2:B6)<0,MAX(B2:B6)>F2,B2<>INT(B2),B3<>INT(B3),B4<>INT(B4),B5<>INT(B5),B6<>INT(B6)),"Invalid inputs",SUMIFS(C2:C6, D2:D6, ">90")))Try the example
Use the original values or change them to test your understanding.