Your Spreadsheet Formulas

This order covers three formulas for your invoice tracker:

1. A status column that shows Overdue or Due soon.
2. A Summary sheet that lists each client once with their unpaid total.
3. A total paid this month in cell H1.

Which version to use: You use Excel 365 and your bookkeeper uses Excel 2016, so each task notes whether it works in both. Tasks 1 and 3 work in both versions unchanged. Task 2 has a 365 version and a 2016-compatible version.

Important: If you and your bookkeeper share the same file, use the Excel 2016 version of Task 2. Newer functions like UNIQUE, SORT and FILTER don't exist in Excel 2016, so they will likely show #NAME? once the file recalculates on their computer.

Key assumptions

• Your dates are US format (month/day/year), so the formulas use comma separators.
• Ranges run to row 500, which leaves room to grow past your ~300 rows.
• "Unpaid" means column E is anything other than "Yes", including blank. This matches invoice 1004.
• "Paid this month" is based on the Paid Date in column G.

Assumed layout

You gave most of the layout. The only thing assumed is that the Summary sheet starts empty with headers in row 1.

Invoices sheet

Summary sheet

Task 1: Overdue / Due soon status

• Put this in: Invoices!F2, then fill down to F500. The formula works in both Excel 365 and 2016.
• Formula:

=IF(D2="","",IF(TRIM(E2)="Yes","",IF(D2<TODAY(),"Overdue",IF(D2<=TODAY()+7,"Due soon",""))))

• What it does: Shows "Overdue" for unpaid invoices whose due date has passed, and "Due soon" for unpaid invoices due within the next 7 days. Otherwise it shows nothing.
• How it works:
  • IF(D2="","", …) shows nothing on rows with no due date, which covers blank rows.
  • IF(TRIM(E2)="Yes","", …) shows nothing for paid invoices. TRIM ignores stray spaces, so "Yes " still counts. The check is not case-sensitive, so "yes" works too.
  • D2<TODAY() checks whether the due date is before today. If so, it shows "Overdue".
  • D2<=TODAY()+7 checks whether the due date is today or within the next 7 days. If so, it shows "Due soon".
  • The final "" covers anything due more than 7 days from now, which shows blank.
• Example: Results depend on today's date, so here are two example "todays":

• Edge cases:
  • Due today counts as "Due soon", not "Overdue". It becomes Overdue the next day.
  • Blank Paid? is treated as unpaid, as with invoice 1004.
  • Dates stored as text (for example, typed with a leading apostrophe or pasted from another system) won't be recognized and will show blank. See the Setup checklist for how to check.
  • Updating: the status updates automatically each day because it uses TODAY().
• Optional tweak: To flag bad dates instead of hiding them, use this version. It shows "Check date" if D contains something that isn't a real date:

=IF(D2="","",IF(NOT(ISNUMBER(D2)),"Check date",IF(TRIM(E2)="Yes","",IF(D2<TODAY(),"Overdue",IF(D2<=TODAY()+7,"Due soon","")))))

Task 2: Summary sheet with each client's unpaid total

Version A: Excel 365 only (auto-updating, sorted A–Z)

• Put this in: Summary!A2 and Summary!B2. Both cells spill down automatically, so don't fill them down. Keep the cells below empty.
• Formula for A2 (the client list):

=SORT(UNIQUE(FILTER(Invoices!$B$2:$B$500,Invoices!$B$2:$B$500<>"")))

• Formula for B2 (the unpaid totals):

=SUMIFS(Invoices!$C$2:$C$500,Invoices!$B$2:$B$500,A2#,Invoices!$E$2:$E$500,"<>Yes")

• What it does: Lists every client once in alphabetical order and adds up their invoices that aren't marked "Yes".
• How it works:
  • FILTER(…,…<>"") removes blank rows from the client column.
  • UNIQUE(…) keeps each client name only once.
  • SORT(…) puts the names in A–Z order.
  • A2# means "the whole spilled list starting in A2", so B2 calculates a total for every client.
  • SUMIFS(Amount, Client, each client, Paid?, "<>Yes") adds amounts where the client matches and Paid? is not "Yes". Blank counts as unpaid.

Version B: Excel 2016 compatible (use this if the file is shared)

• Put this in: Summary!A2 and Summary!B2. Then select A2:B2 and fill down to about row 60, or more if you expect more clients. Extra rows stay blank.
• Formula for A2:

=IFERROR(INDEX(Invoices!$B$2:$B$500,MATCH(0,INDEX(COUNTIF($A$1:A1,Invoices!$B$2:$B$500)+(Invoices!$B$2:$B$500=""),0),0)),"")

• Formula for B2:

=IF(A2="","",SUMIFS(Invoices!$C$2:$C$500,Invoices!$B$2:$B$500,A2,Invoices!$E$2:$E$500,"<>Yes"))

• How the A2 formula works:
  • COUNTIF($A$1:A1, client list) checks, for every client on the Invoices sheet, whether it has already been listed above. The range $A$1:A1 grows as you fill down.
  • +(Invoices!$B$2:$B$500="") also marks blank cells, so they are skipped.
  • MATCH(0, …, 0) finds the first client that isn't listed yet and isn't blank.
  • INDEX(…) returns that client's name.
  • IFERROR(…,"") shows blank once every client has been listed.
  • The inner INDEX(…,0) lets this work as a normal formula in 2016, without Ctrl+Shift+Enter. If it ev