📌 Stage 2 · The Invoice Register · Invoicing & AR · Paid, Open, Status · 40–50 min

SUMIFS applies the cash; the ladder names the state

Learning goals

  • Apply cash receipts to invoices with a SUMIFS keyed on the invoice number
  • Derive the open balance as amount minus applied payments
  • Explain the four-state ladder and why Overdue outranks Partial

Concepts

Applying receipts with SUMIFS

The Paid Amount column does not know about payments as events - it knows the total applied to this invoice number. Whenever a receipt lands in the Cash Receipts journal referencing INV-1094, this column's SUMIFS grows by exactly that amount. Two half payments, one full payment, four dribbles: the register does not care, it just sums.

The sheet name contains a space, so the reference is wrapped in single quotes: 'Cash Receipts'! That quoting is not decoration - without it Excel reads the reference as two separate names and the formula breaks.

=SUMIFS('Cash Receipts'!$F$5:$F$1004,'Cash Receipts'!$D$5:$D$1004,$B6)

Sum the Amount column (F) of the receipts journal, but only rows whose Invoice # (column D) equals this row's invoice number ($B6). Both ranges are pinned to 5:1004 so future receipts are counted automatically.

=$H6-$J6

Open balance is simply the invoiced amount minus everything applied. For INV-1094: $6,200.00 - $3,200.00 = $3,000.00 still owed.

The status ladder

One IF decides the invoice's state, and the order of its questions is the whole trick. First: is the open balance zero or less? Then it is Paid - nothing else matters. Second: is the due date before the as-of date? Then it is Overdue - even if partial payments arrived, because past-due-plus-part-paid is a collection priority, and the Paid Amount column already shows the progress. Third: did any money arrive at all? Partial. Otherwise: Open.

The ladder's verdicts for the September close: four invoices Paid, six Overdue, two Open, and one Partial - INV-1119, where Two Rivers Coffee paid $1,000.00 of $2,400.00 three weeks before the due date.

=IF($K6<=0,"Paid",IF($I6<Settings!$C$7,"Overdue",IF($J6>0,"Partial","Open")))

$K6<=0 catches settled invoices first. $I6<Settings!$C$7 compares the due date to the as-of date for Overdue. $J6>0 catches part-paid invoices still within terms (Partial). Everything else is Open - billed, inside terms, untouched.

Reading the register at a glance

With statuses computed, the register becomes a triage board. Filter or sort by Status and the collection morning writes itself: the six Overdue rows are the work, the two Open rows are patience, the one Partial row deserves a thank-you note, and the four Paid rows are history that must never be deleted - they are the audit trail behind September's $12,950.00 of collections.

Notice what the ladder refuses to do: it never says 'mostly paid' or 'a little late'. Four clean states, computed the same way every day, from cells anyone can check.

💡 Tips:

  • An invoice can never be both Partial and Overdue in this model - that is deliberate. When you need the split, read Paid Amount next to the status.
  • Because status reads Settings!$C$7, an invoice that is Partial today can become Overdue at the next close without anyone touching its row.

Practice

Open on Invoices. Cell J6 (Paid Amount of INV-1094) is blank - retype the application formula and watch the partial-payment story complete itself.

  1. Type the application formula — Cell J6 is blank. Type =SUMIFS('Cash Receipts'!$F$5:$F$1004,'Cash Receipts'!$D$5:$D$1004,$B6) and press Enter. J6 returns 3,200.00 - the Aug 14 payment of 2,000.00 plus the Sep 25 payment of 1,200.00, both referencing INV-1094.
  2. Follow the money down the row — K6 recomputes to 3,000.00 open (6,200.00 - 3,200.00) and L6 stays Overdue: the invoice is two-thirds settled but 70 days past due - a collection priority, exactly as the ladder intends.
  3. Find the other partial stories — INV-1108 (row 9): $4,000.00 of $9,600.00 applied, status Overdue. INV-1119 (row 17): $1,000.00 of $2,400.00 applied and due Oct 24 - the register's one Partial row.
  4. Count the states — Scan column L: 4 Paid, 6 Overdue, 2 Open, 1 Partial. The open balances (column K) of the nine unpaid rows total $36,600.00.

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:

  • ☐ Typed the SUMIFS into J6 and got 3,200.00 applied to INV-1094
  • ☐ Verified K6 shows 3,000.00 open and L6 stays Overdue despite the partial payments
  • ☐ Found the live Partial example: INV-1119 with 1,000.00 of 2,400.00 paid, due Oct 24, 2026
  • ☐ Noted the baseline: statuses run 4 Paid / 6 Overdue / 2 Open / 1 Partial and open balances total $36,600.00