Sum repeated payments for each invoice
Your goal: Combine split receipts and refunds before calculating each invoice balance; keep unmatched receipts visible in the source.
Full 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.
| # | ||
|---|---|---|
| 1 | ||
| 2 | ||
| 3 | ||
| 4 | ||
| 5 | ||
| 6 | ||
| 7 | ||
| 8 | ||
| 9 | ||
| 10 | ||
| 11 |
Collect receipts for A2
Rules for this step
Select every matching payment ID in column D, including repeated receipts, negative refunds and zero amounts. Keep literal identifiers and invoice order. With no matching receipts, explicitly confirm an empty set; the paid total is numeric zero.
- Invoice ID · A2
- "INV-101"
- Invoice amount · B2
- 100
Your selected source cells will appear here.
- Signed paid total
- 0
- Invoice minus paid
- 100