# Know Who Owes You: An Invoice and Payment Sheet for Small Businesses > Invoice numbers that never repeat, a status the sheet works out itself, partial payments, an ageing report and polite reminders, all in Google Sheets. - Author: Tai Pham Phu - Published: 22 Oct 2026 - Tags: Spreadsheets, Small Business, Business, Automation - URL: https://phamphutai.com/2026/10/22/know-who-owes-you-invoice-and-payment-sheet/ "Did Mr. Hùng pay for September?" It sounds like an easy question. Then you open the sheet. There's a yellow cell that says "paid?" with a question mark. There's a bank transfer for 3,000,000 that doesn't match any single invoice. There's an invoice someone deleted because "it was wrong." And there's a column called Status where half the rows say "Sent" even though the money came in weeks ago. Most small businesses don't lose money because customers refuse to pay. They lose it because **nobody is quite sure who still owes what**, so the awkward follow-up never happens. In [The One Google Sheet That Runs a Small Business](https://phamphutai.com/2026/10/14/the-one-google-sheet-that-runs-a-small-business/), I introduced an Invoices tab and a Payments tab. This post goes one level deeper. It's about the details that make those two tabs trustworthy: numbers that never repeat, a status nobody types, payments that don't match invoices one to one, deposits, and a monthly check against the bank. --- ## The example, again **This is a made-up example.** Mai runs a small home-cleaning company in Đà Nẵng, the same fictional business from the first post. Most customers pay on the spot. About twenty are on monthly billing: Mai sends one invoice at the start of the month, and they pay by bank transfer, sometimes in full, sometimes in parts, sometimes late. Two of her customers will show up below: - **Ms. Lan (C014)** pays her 1,800,000 invoice in two parts. - **Mr. Hùng (C022)** owns two apartments and sends one transfer of 3,000,000 to cover two invoices. If you run a tutoring centre, swap "cleaning" for "tuition" and "apartment" for "child." The shape is identical. One honest note before we start. **Invoice and tax rules depend on your country.** Some countries require official invoices in a set format, through an approved system, with rules on numbering and on how to cancel one. Vietnam, for example, has required electronic invoices for most businesses since July 2022, and the details have been updated since. What follows is a sheet for **knowing who owes you**, not a replacement for a legal invoice. Check your local rules, or ask an accountant, before you treat it as one. --- ## The layout Here are the columns. They're a little richer than in the first post, so the formulas below have something to work with. **Invoices** | Col | Name | Typed or formula? | |---|---|---| | A | No. | typed (a plain number, like 102) | | B | InvoiceID | formula | | C | CustomerID | typed | | D | Issue date | typed | | E | Due date | typed | | F | Amount | typed | | G | Sent on | typed (the date you sent it) | | H | Void reason | typed, only if voided | | I | Paid so far | formula | | J | Amount due | formula | | K | Status | formula | | L | Days late | formula | **Payments** | Col | Name | Typed or formula? | |---|---|---| | A | PaymentID | typed | | B | Date | typed | | C | CustomerID | typed | | D | InvoiceID, or `ON ACCOUNT` | typed | | E | Amount | typed | | F | Method | dropdown: Transfer / Cash | | G | Bank reference | typed | | H | Matched on | typed (date, filled at month end) | | I | Check | formula | Notice how few columns you actually type into on the Invoices tab. Everything that can be worked out, is.

Payments point at invoices. The invoice adds them up, and its status follows from the numbers and dates.

--- ## Invoice numbers that never repeat Two invoices with the same number is how arguments start. "I paid INV-0102." "Which one?" The fix is simple, but it has one rule: **the number is typed, not calculated from the row.** It's tempting to use a formula like `=ROW()` or "the cell above plus one." Don't. The moment someone sorts the tab, inserts a row or deletes one, every number below it shifts. Invoice 102 becomes 101, and the customer is holding a PDF with a number that now points at someone else. So column A holds a plain typed number. Column B turns it into a nice ID: ``` =IF(A2="", "", "INV-" & TEXT(A2, "0000")) ``` In words: if there's no number yet, show nothing. Otherwise, show "INV-" followed by the number padded to four digits, so 102 becomes INV-0102. To know which number to type next, put this in a cell at the top of a Settings tab (or anywhere you'll see it): ``` ="Next: INV-" & TEXT(MAX(Invoices!A:A) + 1, "0000") ``` It finds the biggest number used so far and adds one. Because it uses the **biggest**, not the count, a voided invoice still "uses up" its number. That's what you want. Numbers never come back. And as a safety net, highlight any duplicate. Select `A2:A` in Invoices, open **Format > Conditional formatting**, choose **Custom formula is**, and use: ``` =AND($A2 <> "", COUNTIF($A$2:$A, $A2) > 1) ``` In words: if this cell isn't empty and the same number appears more than once in the column, colour it. The `$A2 <> ""` part matters. Without it, every empty row would light up, because empty cells "match" each other. --- ## Never delete an invoice. Void it. If an invoice is wrong, the instinct is to delete the row and start again. Please don't. Deleting leaves a gap in your numbers that nobody can explain later. It also breaks any payment that pointed at that invoice: the money is still in the Payments tab, but now it's pointing at nothing. Instead, **write a reason in the Void reason column (H)**, something like "Wrong price, replaced by INV-0131." Then create a new invoice with the next number. The old row stays, clearly marked, with its amount due set to zero by the formulas below. If a payment was already made against the voided invoice, edit that payment row so it points at the replacement invoice instead. In some countries, once a legal invoice has been issued, you can't simply cancel it. You may need a formal adjustment or credit note. Your sheet's "void" is for your own tracking. Check what your local rules ask for the official document. --- ## Status: worked out, never typed A typed status is correct on the day you type it. A week later, the customer pays, and nobody changes "Sent" to "Paid." Or the due date passes, and nothing turns "Overdue" by itself. So the status is a formula. First, two helper columns. **Paid so far (I2):** ``` =IF(B2="", "", SUMIFS(Payments!E:E, Payments!D:D, B2)) ``` In words: add up every payment amount in Payments where the InvoiceID matches this invoice. **Amount due (J2):** ``` =IF(B2="", "", IF(H2 <> "", 0, F2 - I2)) ``` In words: if the invoice is void, nothing is due. Otherwise, it's the amount minus what's been paid. **Status (K2):** ``` =IF(B2="", "", IFS(H2 <> "", "Void", G2 = "", "Draft", J2 <= 0, "Paid", E2 < TODAY(), "Overdue", I2 > 0, "Part-paid", G2 <> "", "Sent")) ``` IFS checks each condition in order and stops at the first one that's true. So read it top to bottom: 1. Has a void reason? **Void.** 2. Not sent yet? **Draft.** 3. Nothing left to pay? **Paid.** 4. Past the due date with money still owed? **Overdue.** 5. Some money in, but not all? **Part-paid.** 6. Otherwise, it's been sent and is waiting. **Sent.** The order is the whole trick. "Paid" comes before "Overdue," so a late invoice that's now fully paid shows as Paid. "Overdue" comes before "Part-paid," because a late invoice is the more urgent fact, even if some money came in. The Paid so far column still shows the partial amount. **Days late (L2):** ``` =IF(K2 = "Overdue", TODAY() - E2, "") ``` Dates in Google Sheets are numbers underneath, so today minus the due date gives a number of days. One small note: TODAY updates when the sheet recalculates, not every second, which is fine for this. If a customer pays too much, J2 goes negative and the status shows Paid. Keep an eye out for negative amounts due. That money belongs to the customer, either as a refund or as credit toward the next invoice. --- ## Partial payments and one payment for many invoices This is where a "Paid" tick box falls apart, and a Payments tab earns its keep. **The rule: one Payments row per slice of money per invoice.** Ms. Lan pays her 1,800,000 invoice in two parts. That's two rows: | PaymentID | Date | CustomerID | InvoiceID | Amount | |---|---|---|---|---| | P0457 | 2026-10-06 | C014 | INV-0102 | 1,000,000 | | P0463 | 2026-10-13 | C014 | INV-0102 | 800,000 | After the first row, INV-0102 shows **Part-paid**. After the second, **Paid**. Nobody touched the Invoices tab. Mr. Hùng sends one transfer of 3,000,000 for two apartments. That's also two rows, sharing the same PaymentID and bank reference: | PaymentID | Date | CustomerID | InvoiceID | Amount | |---|---|---|---|---| | P0460 | 2026-10-09 | C022 | INV-0108 | 1,500,000 | | P0460 | 2026-10-09 | C022 | INV-0109 | 1,500,000 | The two slices add up to the transfer. Each invoice gets exactly its share. When you later check the bank, you'll look for one transfer of 3,000,000 with that reference, and find it. To catch mistakes, the **Check (I2)** column in Payments confirms the invoice really belongs to the customer who paid: ``` =IF(OR(A2 = "", D2 = "ON ACCOUNT"), "", IF(XLOOKUP(D2, Invoices!B:B, Invoices!C:C, "") = C2, "OK", "Check")) ``` In words: look up which customer owns this invoice. If it's the same customer who paid, say OK. If not, or the invoice doesn't exist, say Check. A typo like INV-0180 instead of INV-0108 shows up straight away. --- ## Deposits Some jobs need money up front: a big move-out clean, or a full term of tutoring. There are two clean ways to handle it. **Option 1: a deposit invoice.** Issue INV-0130 for the deposit, and later INV-0141 for the remainder. Simple, and each piece of paper matches each payment. This is the better choice if the customer needs a document for the deposit, or if your local rules say a deposit needs its own invoice. **Option 2: money "on account."** Record the deposit in Payments with `ON ACCOUNT` in the InvoiceID column. It sits there as the customer's credit. When the job is done and you issue the full invoice, edit that payment row and replace `ON ACCOUNT` with the new invoice number. The invoice immediately shows the deposit as Paid so far. `ON ACCOUNT` is also where an overpayment goes, or a transfer that arrives before you've decided which invoice it's for. Just don't let it pile up. Money on account is money you haven't finished thinking about. --- ## The "who owes me" tab with an ageing report An **ageing report** groups what you're owed by how late it is. It answers a sharper question than "who owes me?" It answers "**who should I chase first?**" Twenty days late is a nudge. Ninety days late is a phone call. Make a new tab called **Owed**. Nothing is typed here. **Column A, the list of customers who owe something (A2):** ``` =IFERROR(SORT(UNIQUE(FILTER(Invoices!C2:C, Invoices!C2:C <> "", Invoices!J2:J > 0, Invoices!K2:K <> "Draft")), 1, TRUE), "Nobody owes anything") ``` In words: take the CustomerID of every invoice that has money due and has actually been sent, remove duplicates, and sort them. If nobody owes anything, say so instead of showing an error. **Column B, the name:** ``` =IF($A2 = "", "", XLOOKUP($A2, Customers!A:A, Customers!B:B, "Unknown ID")) ``` **Column C, not due yet:** ``` =IF($A2 = "", "", SUMIFS(Invoices!$J:$J, Invoices!$C:$C, $A2, Invoices!$K:$K, "Sent") + SUMIFS(Invoices!$J:$J, Invoices!$C:$C, $A2, Invoices!$K:$K, "Part-paid")) ``` **Columns D to G, the ageing buckets.** Each one adds up the amount due for this customer where Days late falls in a range. 0 to 30 days late (D2): ``` =IF($A2 = "", "", SUMIFS(Invoices!$J:$J, Invoices!$C:$C, $A2, Invoices!$L:$L, ">0", Invoices!$L:$L, "<=30")) ``` 31 to 60 days late (E2): ``` =IF($A2 = "", "", SUMIFS(Invoices!$J:$J, Invoices!$C:$C, $A2, Invoices!$L:$L, ">30", Invoices!$L:$L, "<=60")) ``` 61 to 90 days late (F2): ``` =IF($A2 = "", "", SUMIFS(Invoices!$J:$J, Invoices!$C:$C, $A2, Invoices!$L:$L, ">60", Invoices!$L:$L, "<=90")) ``` Over 90 days late (G2): ``` =IF($A2 = "", "", SUMIFS(Invoices!$J:$J, Invoices!$C:$C, $A2, Invoices!$L:$L, ">90")) ``` Because Days late is blank for anything that isn't overdue, only overdue invoices land in these four columns. **Column H, total owed:** ``` =IF($A2 = "", "", SUM(C2:G2)) ``` **Column I, money on account (credit):** ``` =IF($A2 = "", "", SUMIFS(Payments!$E:$E, Payments!$C:$C, $A2, Payments!$D:$D, "ON ACCOUNT")) ``` Copy B2 to I2 down as far as you expect customers to go. The `IF($A2 = "", "", ...)` wrapper keeps the empty rows quiet. At the top of the tab, two numbers are enough: ``` =SUM(H2:H) ``` ``` =SUM(F2:G) ``` The first is everything you're owed. The second is everything more than 60 days late. If the second number is growing month after month, that's not a cash problem yet. It's a habit problem, and it's much cheaper to fix now. For Mai, in this made-up example, the tab might show Ms. Lan with nothing due (she's paid), a café owner with 900,000 in the 31 to 60 column, and Mr. Hùng with October's invoices sitting in "not due yet." Mai calls the café owner. Everyone else gets nothing, because nothing is needed. --- ## Polite reminders, in three stages Most late payers aren't dishonest. They're busy, like you. A short, friendly, specific message gets most invoices paid. The trick is to make it **easy to pay right now**: the number, the amount and how to pay, all in one message. Copy these into a Templates tab, swap the brackets, and paste into WhatsApp, Zalo or email. **Stage 1, on the due date:** ``` Hi [Name], a quick note that invoice [INV-0102] for [1,800,000 VND] is due today. You can pay by transfer to [bank, account name, number], with [INV-0102] in the message. Thank you, and thanks for choosing us! ``` **Stage 2, about a week late:** ``` Hi [Name], I hope all is well. Our records show invoice [INV-0102] for [1,800,000 VND], due on [8 Oct], is still open. If you've already paid, please send me the transfer details and I'll match it up. If not, you can pay to [bank details] with [INV-0102] in the message. Thank you! ``` **Stage 3, about a month late:** ``` Hi [Name], I'm following up on invoice [INV-0102] for [1,800,000 VND], now about a month overdue. I'd like to sort this out together. Could you let me know by [date] when you can pay? If it's easier, we can split it into two payments. Our bank details are [bank details]. Thank you for your understanding. ``` Notice what stage 2 does. "If you've already paid, send me the details" gives the customer a polite way out, and it catches the times **you** made the mistake. That happens more than people admit. Stage 3 is firmer, but still kind. It asks for a date and offers a way to make it easier. After stage 3, it's a phone call, not a fourth message. --- ## Monthly reconciliation against the bank **Reconciliation** means checking that your sheet and your bank agree. It's the step most people skip, and it's the one that catches the real problems: a payment you forgot to record, a payment recorded twice, a transfer from someone you can't identify. Once a month, take 30 minutes: 1. **Download or open your bank statement** for the month. 2. **For each incoming customer transfer**, find its row (or rows) in Payments. Use the bank reference to match. Type the statement date into **Matched on (H)**. 3. **Anything in the bank but not in the sheet?** Add it to Payments. If you know the customer but not the invoice, use `ON ACCOUNT`. If you don't know who sent it, find out before you record it. Don't guess. 4. **Anything in the sheet but not in the bank?** That's the worrying one. Someone recorded money that never arrived. Fix the row, and that invoice's status will change back on its own. Two numbers make it quick. Transfers recorded in October: ``` =SUMIFS(Payments!E:E, Payments!F:F, "Transfer", Payments!B:B, ">=" & DATE(2026,10,1), Payments!B:B, "<" & DATE(2026,11,1)) ``` And of those, the ones matched to the bank: ``` =SUMIFS(Payments!E:E, Payments!F:F, "Transfer", Payments!B:B, ">=" & DATE(2026,10,1), Payments!B:B, "<" & DATE(2026,11,1), Payments!H:H, ">0") ``` The `">0"` works because a date in Google Sheets is a number underneath, so any filled-in date counts. When both numbers agree, and they match the customer transfers on your statement, you're done. If they don't, the difference tells you how much to look for. Cash is simpler: count it, bank it, and record the deposit. Then name a version of the file (**File > Version history > Name current version**), something like "October, reconciled." That's your line in the sand. --- ## Build it, or start from a template Everything above is a few hours of careful work in Google Sheets. If you'd rather start with the formulas already written, [HiTaVa](https://phamphutai.com/projects/hitava/), the template business I co-founded, has a ready-made invoicing template you can start from. Either path is fine. What matters is that the structure is right. And if you're wondering whether a sheet is still the right tool at all, I wrote about where that line is in [Your Spreadsheet Keeps Beating Every App You Try. Here's When to Switch.](https://phamphutai.com/2026/10/14/your-spreadsheet-keeps-beating-every-app/) For invoices and payments at a small business's volume, a well-built sheet usually holds up for a long time. --- ## The takeaway Knowing who owes you isn't about chasing harder. It's about **never being unsure**. - **Type the invoice number. Never calculate it from the row.** Numbers never repeat, and never come back. - **Void, don't delete.** Every number stays explained. - **Status is a formula.** Draft, Sent, Part-paid, Paid, Overdue all follow from dates and payments. - **One Payments row per slice of money.** Partial payments and one transfer for several invoices both just work. - **An ageing report tells you who to chase first.** Short list, oldest first. - **Three polite messages, then a phone call.** - **Once a month, make the sheet and the bank agree.** And check your country's invoice rules. The sheet tells you who owes you. The law decides what an invoice has to look like. --- *Sources: Google Docs Editors Help pages for [SUMIFS](https://support.google.com/docs/answer/3238496), [IFS](https://support.google.com/docs/answer/7014145), [COUNTIF](https://support.google.com/docs/answer/3093480), [UNIQUE](https://support.google.com/docs/answer/10522653), [FILTER](https://support.google.com/docs/answer/3093197), [SORT](https://support.google.com/docs/answer/3093150), [XLOOKUP](https://support.google.com/docs/answer/12405947), [TEXT](https://support.google.com/docs/answer/3094139), [TODAY](https://support.google.com/docs/answer/3092984) and [conditional formatting](https://support.google.com/docs/answer/78413), checked October 2026. On Vietnam's e-invoicing start date: [Vietnam Briefing](https://www.vietnam-briefing.com/news/vietnam-e-invoice-implementation-prepare-for-july-2022.html/) and [Fonoa](https://www.fonoa.com/resources/blog/official-mandatory-application-of-e-invoices-in-vietnam).* *Related: [The One Google Sheet That Runs a Small Business](https://phamphutai.com/2026/10/14/the-one-google-sheet-that-runs-a-small-business/), and [Boring Businesses Make the Best Businesses](https://phamphutai.com/2026/10/07/boring-businesses-make-the-best-businesses/).*