How to Build an Accounts Receivable Aging Report in Google Sheets
Who owes you, and how late they are: unpaid invoices sorted into the usual buckets, with a count and amount for each.
The short answer: add a days-past-due column, =IF(F2<>"", "", $I$1-D2), then total each bucket with SUMIFS. In the sample data, $222,876 was unpaid on October 31, 2026, and $92,840 of it was past due.
The sample data
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Invoice | Customer | Invoice date | Due date | Amount | Paid date |
| 2 | INV-26001 | Tin Roof Kitchen | 2026-01-01 | 2026-01-31 | 288 | 2026-01-30 |
| 3 | INV-26002 | Corner Coffee House | 2026-01-01 | 2026-01-31 | 1606 | 2026-01-19 |
| 4 | INV-26003 | Corner Coffee House | 2026-01-01 | 2026-01-31 | 1144 | 2026-02-02 |
| 5 | INV-26004 | Foxglove Café | 2026-01-01 | 2026-01-31 | 192 | 2026-02-03 |
| 6 | INV-26005 | Mill Pond Bistro | 2026-01-01 | 2026-01-31 | 1240 | 2026-03-16 |
| … | 2,089 more rows | |||||
Sample data: a tab named Invoices with 2,094 invoices issued in 2026 on Net 30 terms, as of October 31, 2026, one row per invoice. Paid date is blank while the invoice is unpaid. Download invoices.csv and import it (File → Import) to follow along. The business is fictional.
The classic way: a days-past-due column, then SUMIFS by bucket
The Invoices tab has Invoice (A), Customer (B), Invoice date (C), Due date (D), Amount (E) and Paid date (F, blank while unpaid). Put the as-of date in I1: =DATE(2026,10,31), or =TODAY() for a live report.
Type Days past due in G1 and, in G2 copied down, the days since the due date for unpaid invoices only:
=IF(OR(A2="", F2<>""), "", $I$1-D2)Zero or negative means not yet due. Now on a summary tab, one row per bucket. Count and amount for each:
Current: =COUNTIFS(Invoices!$G:$G, "<=0") =SUMIFS(Invoices!$E:$E, Invoices!$G:$G, "<=0")
1-30 days: =COUNTIFS(Invoices!$G:$G, ">=1", Invoices!$G:$G, "<=30") =SUMIFS(Invoices!$E:$E, Invoices!$G:$G, ">=1", Invoices!$G:$G, "<=30")
31-60 days: =COUNTIFS(Invoices!$G:$G, ">=31", Invoices!$G:$G, "<=60") =SUMIFS(Invoices!$E:$E, Invoices!$G:$G, ">=31", Invoices!$G:$G, "<=60")
61-90 days: =COUNTIFS(Invoices!$G:$G, ">=61", Invoices!$G:$G, "<=90") =SUMIFS(Invoices!$E:$E, Invoices!$G:$G, ">=61", Invoices!$G:$G, "<=90")
Over 90 days: =COUNTIFS(Invoices!$G:$G, ">90") =SUMIFS(Invoices!$E:$E, Invoices!$G:$G, ">90")Paid invoices have a blank in G, and the number conditions skip blanks and the heading, so only unpaid invoices are counted. The Total open row sums both columns.
Example on sample data| A | B | C | |
|---|---|---|---|
| 1 | Bucket | Invoices | Amount |
| 2 | Current | 210 | $130,036 |
| 3 | 1-30 days | 67 | $42,298 |
| 4 | 31-60 days | 31 | $20,834 |
| 5 | 61-90 days | 12 | $8,608 |
| 6 | Over 90 days | 37 | $21,100 |
| 7 | Total open | 357 | $222,876 |
Computed from the sample CSV above. Cells formatted as currency and percent.
Who is in the over-90 bucket? =QUERY(Invoices!A:G, "select B, count(A), sum(E) where G > 90 group by B order by sum(E) desc label count(A) 'Invoices', sum(E) 'Amount'", 1) lists the customers behind the oldest debt, worst first. That list is the collections call sheet.
Gotchas
- Age from the due date, not the invoice date. An invoice on Net 30 terms is not late on day 20. Some aging reports age from the invoice date; if you compare with your accounting system, check which one it uses.
- Partial payments. This layout treats an invoice as paid or not. If customers pay in parts, add an amount-paid column and age the balance (
Amount - Paid) instead. - TODAY() moves. With
=TODAY()in I1 the buckets change every day. Use a fixed date when you send a month-end report so the numbers match what you sent.
The one-cell way: =QUIRIZ
With the Quiriz add-on for Google Sheets, you type the question instead of building the formula. =QUIRIZ does not read the cells around it: it answers from the datasets in your Quiriz account. For this example, upload invoices.csv as a dataset, or connect QuickBooks. You can also add a Google Sheet itself as a dataset that refreshes on a schedule. Then, in any empty cell:
=QUIRIZ("unpaid invoices by aging bucket as of October 31, 2026, by days past the due date: current, 1-30, 31-60, 61-90 and over 90 days, with invoice count and amount and a total row", "table")The "table" mode spills the answer down and to the right from that cell, header row first:
| A | B | C | |
|---|---|---|---|
| 1 | Bucket | Invoices | Amount |
| 2 | Current | 210 | $130,036 |
| 3 | 1-30 days | 67 | $42,298 |
| 4 | 31-60 days | 31 | $20,834 |
| 5 | 61-90 days | 12 | $8,608 |
| 6 | Over 90 days | 37 | $21,100 |
| 7 | Total open | 357 | $222,876 |
Same figures as the formula result above, from the same sample data. The spilled cells are plain numbers, so you can format them, total them or chart them; here they are formatted as currency and percent. Column headings are written with each answer and may be worded differently.
Want one number instead of a table? The "short" mode returns a single value you can format and point other formulas at:
=QUIRIZ("total past-due receivables as of October 31, 2026", "short")| A | |
|---|---|
| 1 | 92840 |
The cell holds 92840; formatted as currency it reads $92,840.
The other modes: "verbose" (the default) answers in a sentence, "bullets" writes two to four points one per cell, and "notes" adds how the figure was calculated. Install Quiriz for Google Sheets and try the question on your own data.
Ask the question, get the table
Quiriz answers business questions in a Google Sheets cell, from your imported files, scheduled Google Sheets and connected tools. Free to start.
Install for Google Sheets →Free on the Google Workspace Marketplace. Or try it on sample data first, no signup.