DEV Community

Ashish Srivastava
Ashish Srivastava

Posted on Originally published at ashishexcelnotes.hashnode.dev

How to Sort Unpaid Invoices into Overdue Groups in a Spreadsheet

#excel #spreadsheets #productivity #tutorial

Written with AI assistance. The formulas below were checked in LibreOffice Calc on sample rows.

"Who owes me money?" is the easy question. The useful one is "how late is it?" An invoice that's 5 days late needs a friendly reminder. One that's 75 days late needs a phone call today. Grouping unpaid invoices by how overdue they are (accountants call this an aging report) tells you where to spend your Monday morning.

Here's how to build one with four columns of formulas, a five-row summary and one check cell. It uses only standard functions (IF, OR, MAX, SUMIFS, COUNTIFS, SUMIF, COUNTIF, ROUND), with no macros and no newer dynamic-array functions.

1. The invoice list

One row per invoice, starting at row 5. Put an As of date in B2: either type a date, or use =TODAY() if you always want "now".

Column What goes in it
A Invoice number
B Customer
C Invoice date
D Due date
E Amount
F Paid so far
G Balance (formula)
H Days overdue (formula)
I Group (formula)

Type in A–F. G, H and I are formulas you fill down.

2. Balance 

=IF(A5="","",E5-F5)
Enter fullscreen mode Exit fullscreen mode

The IF(A5="","",…) wrapper keeps empty rows empty, so you can fill the formulas down 100 rows in advance.

3. Days overdue 

=IF(A5="","",IF(OR(G5<=0,D5=""),"",MAX(0,$B$2-D5)))
Enter fullscreen mode Exit fullscreen mode
  • A paid invoice (balance 0 or less) gets no number at all.
  • A missing due date also gets no number. Without that guard, the as-of date minus an empty cell is a huge number, and the invoice would land in "90+".
  • MAX(0, …) turns "due in 5 days" into 0 instead of −5.

4. The group 

=IF(A5="","",IF(G5<=0,"Paid",IF(D5="","Add due date",IF(H5=0,"Not due",IF(H5<=30,"1-30",IF(H5<=60,"31-60",IF(H5<=90,"61-90","90+")))))))
Enter fullscreen mode Exit fullscreen mode

Read it left to right: paid → missing due date → not due yet → 1–30 → 31–60 → 61–90 → over 90. Watch the boundaries. An invoice due today is 0 days overdue, so it's "Not due". Day 30 is still "1-30"; day 31 is "31-60".

I use plain hyphens ("1-30") rather than dashes, because the summary looks these labels up by exact text, and a typed hyphen is easier to match.

5. The summary

In K5:K9, type the five labels exactly as the formula writes them: Not due, 1-30, 31-60, 61-90, 90+. Next to them:

Amount owed in each group (L5, fill down):

=SUMIFS($G$5:$G$104,$I$5:$I$104,K5)
Enter fullscreen mode Exit fullscreen mode

Number of invoices in each group (M5, fill down):

=COUNTIFS($I$5:$I$104,K5)
Enter fullscreen mode Exit fullscreen mode

Total owed (L10). Only positive balances, so an overpayment doesn't cancel out a real debt:

=SUMIF($G$5:$G$104,">0")
Enter fullscreen mode Exit fullscreen mode

Overdue only, 1+ days (L11):

=SUM(L6:L9)
Enter fullscreen mode Exit fullscreen mode

Oldest overdue, in days (L13):

=MAX(H5:H104)
Enter fullscreen mode Exit fullscreen mode

6. The check cell (don't skip this) 

=IF(ROUND(SUM(L5:L9)-L10,2)=0,"OK","CHECK")
Enter fullscreen mode Exit fullscreen mode

If the groups don't add up to the total owed, something has slipped through. In my sample data it showed CHECK for exactly the right reason: an invoice with no due date isn't in any of the five groups. A count makes that visible (L14):

=COUNTIF($I$5:$I$104,"Add due date")
Enter fullscreen mode Exit fullscreen mode

Fill in the due date and the check goes back to OK.

7. Test it with awkward rows

Before you trust the totals, add these sample rows and confirm each lands where you expect:

Test row Expected group
Paid in full Paid (no days shown)
Part-paid, due in 5 days Not due
Due today, unpaid Not due (0 days)
1, 30, 31, 60, 61, 90 and 91 days late 1-30, 1-30, 31-60, 31-60, 61-90, 61-90, 90+
Paid more than invoiced Paid; the negative balance stays out of the total
No due date Add due date (and the check cell shows CHECK)
A completely blank row stays blank

With twelve sample invoices (as of 15 Oct 2026), my groups came out as: Not due 10,000 (2 invoices), 1-30 9,500 (2), 31-60 8,000 (2), 61-90 11,500 (2), 90+ 1,500 (1). The total owed was 42,500 across 10 invoices, including the one with no due date, so the check cell said CHECK until it was fixed. Your numbers will differ; what matters is that every row lands where you expected.

8. Using it every week

  • Update Paid so far the day money arrives, so the balance is always true.
  • Monday: sort or filter by Group, oldest first, and chase on a rhythm (a reminder at 1–30, a call at 31–60, a firm plan beyond that).
  • Credits and overpayments show as negative balances in the Paid group. Settle them with the customer rather than letting them hide.

This is a tracking method, not accounting or tax advice. Your accountant or CA may want aging done differently for your books.

Top comments (0)