Only the first receipt is counted
Aggregate every matching payment, including refunds.
Sum repeated payments for each invoice
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5 to return the eight invoice IDs from A2:A9, their total signed payments from D2:E11, and invoice amount minus paid total. Match text IDs literally and without ASCII letter-case differences. Preserve invoice order and spelling. Invoice IDs must be distinct ignoring case; payment IDs may repeat. Blank payment rows are ignored.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:I12. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Combine split receipts and refunds before calculating each invoice balance; keep unmatched receipts visible in the source.
THE QUICK ANSWER01
Combine split receipts and refunds before calculating each invoice balance; keep unmatched receipts visible in the source.
ƒx
=LET(paid,MAP(A2:A9,LAMBDA(key,SUMPRODUCT((D2:D11=key)*E2:E11))),HSTACK(A2:A9,paid,B2:B9-paid))| Invoice ID | Paid total | Balance |
|---|---|---|
| INV-101 | 100 | 0 |
| INV-102 | 150 | 100 |
| INV-103 | 90 | -10 |
| INV-104 | 0 | 0 |
| INV-105 | 125.5 | 0 |
| INV-106 | 0 | 60 |
| INV-107 | 75 | 0 |
| INV-108 | 0 | 200 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
One-to-many matching
The first original invoice has receipts of 40 and 60, so its paid total is 100 and balance zero. INV-102 has 150 paid against 250, leaving 100. A first-match lookup would lose a receipt.
A matching policy
IDs are text with 1–40 printable ASCII characters. Letter case is ignored, but spaces, punctuation and leading zeroes remain significant. Invoice IDs are unique under that rule. Repeated payment IDs are intentional.
Signed cents and blank rows
Amounts are finite numeric values within ±1,000,000 with at most two decimal places. A blank payment ID must have a blank or numeric-zero amount. Numeric-looking text, booleans, whitespace-only IDs and amounts without an ID need preparation before grading.
Unmatched receipts stay available
The original UNALLOCATED receipt does not belong to an invoice row and therefore is not forced onto one. Use the grouped reconciliation task to expose keys that occur on only one side. This task does not claim a complete accounting reconciliation.
Read the full step-by-step walkthrough
- Read one invoice ID at a time
MAP passes each A2:A9 ID to key. One receipt row is not necessarily the whole payment.
- Build a literal comparison mask
Compare every payment ID with the invoice ID. A* and Q? are data here, rather than wildcard criteria.
- Add every signed receipt
Multiply the mask by the payment amounts and sum the products. Refunds remain negative, and an invoice with no receipt has a paid total of zero.
- Keep the paid totals as one reusable array
LET names the eight totals paid so the balance column uses exactly the same aggregation.
- Reconcile the balance
Subtract paid from the invoice amounts. A negative balance is an overpayment under this rule, and a negative invoice amount is a credit.
- Keep the result aligned
HSTACK attaches invoice IDs, paid totals and balances in original invoice order. One formula fills all 24 cells.
Things to watch
A* absorbs AX payments
Use literal equality, or deliberately escape wildcard criteria.
The original rows are reordered
Keep invoice order when attaching the result columns.
About this example
These original single-anchor tasks use a declared ASCII ID policy, signed numeric cents and bounded source rows. Browser calculations and independent rules are checked separately from native Excel/Sheets recalculation, editing and save/reopen.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.