Learning goals

Record payments and collect each invoice's total with SUMIFS
Derive open balance and Paid / Open / Overdue status by formula
Read the cash trio: invoiced, collected, open - and where this course ends

Concepts

Cash in, matched by invoice number

Three payments in the case: $12,000 by ACH (the Bluebird program, paid in full), $6,500 by check #2031 (Harborview equipment, paid in full), and $15,000 by ACH against the $28,000 Cedar Hollow build-out - a partial payment, exactly the kind a naive ledger mishandles. The Payments sheet is deliberately minimal: number, date, invoice number, amount, method (Check / ACH / Card), note. The invoice number is the join that makes receivables computable.

=SUMIFS(Payments!$E$5:$E$1004,Payments!$D$5:$D$1004,$B5)

Sum every payment amount (column E) whose invoice number (column D) equals this row's invoice in B5. INV-1001 has exactly one payment: $6,500.00. Partials need no special handling - add a second $5,000 row and the sum simply grows.

💡 Tips:

SUMIFS is SUMIF's more disciplined sibling: sum range FIRST, then criteria pairs. Same convention as COUNTIFS from Lesson 13.

Balance, status, and the hand-off

Open balance = amount minus paid (I5 = F5 - H5). Status classifies it: balance at or below zero means Paid; otherwise, if the due date passed the aging date it is Overdue, else Open. Involving the same Settings!C6 aging date as everything else, the census lands at two Paid, two Open, one Overdue - INV-1004, the $15,500 Foxglove kit that came due Jun 11, 2026 with no payment against it.

This sheet is the course's final stop and the next course's first stop: with $70,200 invoiced, $33,500 collected, and $36,700 open (of which $15,500 is overdue), the receivables work - aging buckets, statement runs, dunning cadence - is precisely what the companion accounts-receivable course takes over. The pipeline workbook hands off a clean ledger: every open dollar traceable to an order, a quote, an opportunity, and a company.

=IF($I5<=0,"Paid",IF($G5<Settings!$C$6,"Overdue","Open"))

The invoice status: paid off reads Paid; a remaining balance past its due date reads Overdue; anything else reads Open. INV-1004: balance $15,500, due Jun 11, 2026 before the Jun 30 aging date - Overdue.

💡 Tips:

Overdue invoices get conditional formatting here too (red) - the same text-match pattern from Lesson 12, applied to money.

Practice

Fixed case (Jan-Jun 2026, aging date Jun 30, 2026). Pale-green cells are formulas; white cells are typed entries. This lesson opens on Invoices.

Type the payments SUMIFS — H5 is blank. Click it and type =SUMIFS(Payments!$E$5:$E$1004,Payments!$D$5:$D$1004,$B5) then Enter. INV-1001 returns $6,500.00 - check #2031 found and summed.
Watch full payment resolve — Same row: I5 (open balance) shows $0.00 and J5 reads Paid. Amount in, cash out, balance zero - the arithmetic of a clean ledger.
Read a partial and an overdue — Row 7 (INV-1003): paid $15,000.00 of $28,000.00, open $13,000.00, status Open (due Jul 2 - not yet late). Row 8 (INV-1004): paid $0.00, open $15,500.00, status Overdue (due Jun 11).
Take the cash trio — Flip to Dashboard: invoiced $70,200.00, collected $33,500.00, open $36,700.00, overdue $15,500.00. Those four numbers are the hand-off package to the AR course.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

☐ H5 returned $6,500.00 and INV-1001 resolved to Paid with a $0.00 balance
☐ Verified the partial (INV-1003 open $13,000.00) and the overdue (INV-1004 open $15,500.00)
☐ Confirmed the hand-off figures: $70,200 invoiced, $33,500 collected, $36,700 open, $15,500 overdue

📈 Lesson 18 · Payments and open balances