When a customer pays one lump sum for several invoices, record the bank credit once and split it across the invoices in a small allocation table. Each invoice then shows how much has been allocated and how much remains, and a check cell confirms the allocations add up to the bank amount exactly.
What are the three small tables?
Keep them side by side on one tab:
- Invoices: invoice number in A, invoice date in B, amount due in C, remaining balance in D, status in E.
- Bank credit: payment reference in G, date in H, amount in I.
- Allocations: payment reference in K, invoice number in L, allocated amount in M, one row per invoice paid.
The bank credit is the single source of truth for what arrived. The allocation table says which invoices it paid. Nothing is typed twice.
Worked example: one credit, three invoices
Say a customer sends ₹58,000 on 15 November against three invoices. The numbers are made up.
| Invoice | Amount due | Allocated | Remaining | Status |
|---|---|---|---|---|
| INV-101 | 18,000 | 18,000 | 0 | Paid |
| INV-102 | 32,500 | 32,500 | 0 | Paid |
| INV-103 | 9,750 | 7,500 | 2,250 | Part-paid |
| Total | 60,250 | 58,000 | 2,250 | 1 part-paid |
The check: 18,000 + 32,500 + 7,500 = 58,000, which equals the bank credit. The remaining column agrees too: 60,250 due less 58,000 received is 2,250.
Can the sheet allocate the money for you?
If you apply payments to the oldest invoice first, one formula does it. Put the payment amount in I2 (the bank credit table) and list the invoices in order from oldest at the top. In a helper column F beside the invoices, filled down:
=MAX(0, MIN(C2, $I$2 - SUM(C$1:C1)))
SUM(C$1:C1) adds the amounts due above the current row, so the formula asks how much of the payment is left after settling every older invoice, then caps it at this invoice's own amount. C1 is a header and SUM ignores text, so the first row works without a special case. For invoice INV-103 it computes MIN(9,750, 58,000 - 50,500) = 7,500. This only suits a payment applied oldest first; if the customer names the invoices, type the allocations by hand.
How do you work out the remaining balance and status?
Remaining balance per invoice, filled down, subtracts everything allocated to it across all payments:
=C2 - SUMIFS($M$2:$M$200, $L$2:$L$200, A2)
Status uses that result:
=IF(D2=0, "Paid", IF(D2=C2, "Open", "Part-paid"))
Summing across all allocations by invoice number means a later payment simply adds another row, and the same formula keeps working.
Two practical notes. First, if the customer pays the same lump sum for invoices dated across two months, allocate by invoice date and not by payment date, so the month of the invoice shows the right outstanding balance. Second, keep the allocation rows even after an invoice is fully paid. They are the audit trail that shows how a particular bank credit was used.
How do you check the split adds up to the bank amount?
For each payment, in a check column beside the bank table:
=ROUND(SUMIFS($M$2:$M$200, $K$2:$K$200, G2) - I2, 2)
A result of 0 means every rupee received has a home. A positive number means you have allocated more than arrived; a negative number means some of the payment is still unassigned, which is worth a note rather than a guess.
Before you close the books on this payment
- Confirm the bank date and amount against your statement, not the customer's email.
- Check that each invoice number in the allocation table exists in the invoice list.
- Make sure the check cell shows 0 for this payment.
- Note who told you how to apply the payment, and when.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

