Paid or outstanding? (SUMIFS + IF)
The problem
Problem statement
Month-end reconciliation: the invoice list is in A2:B5 and the payment log in D2:E8, where one invoice can be paid in several instalments. For the invoice in A2, put "Paid" or "Outstanding" in the Status cell G2.
Judge by the total paid for that invoice, not by whether a single payment row exists — one instalment does not settle a 500 invoice.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | E | G | |
|---|---|---|---|---|---|
| 1 | Invoice | Invoiced | Invoice | Paid | Status |
| 2 | INV-501 | 500 | INV-501 | 200 | |
| 3 | INV-502 | 320 | INV-501 | 300 | |
| 4 | INV-503 | 180 | INV-502 | 120 | |
| 5 | INV-504 | 900 | INV-503 | 180 | |
| 6 | INV-504 | 400 | |||
| 7 | INV-504 | 400 | |||
| 8 | INV-502 | 200 |
Notes
The payment log has many rows per invoice, so VLOOKUP would only find the first instalment. SUMIFS collapses the log to one total per invoice, and IF turns that into a status. Partial payments must come out as Outstanding, so the comparison is >= against the invoiced amount.
How to approach
Your answer goes in G2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is G2
- Total the payment log for A2 first: SUMIFS
- Compare that total with B2
- Shape: =IF(?, ?, ?) — the condition is a comparison, not a lookup
Official solutions
Show 2 solutions — try it yourself first
Approach 1: SUMIFS inside IF
=IF(SUMIFS($E$2:$E$8, $D$2:$D$8, A2) >= B2, "Paid", "Outstanding")
INV-501 has two payments, 200 + 300 = 500, which equals the invoiced 500, so the status is Paid. Change one instalment and the same formula returns Outstanding.
Approach 2: SUMIF with the sum range last
=IF(SUMIF($D$2:$D$8, A2, $E$2:$E$8) >= B2, "Paid", "Outstanding")
SUMIF takes the criteria range first and the sum range last; the logic is otherwise identical. Reversing those two ranges is the classic SUMIF/SUMIFS mix-up.
Function guide
FAQ
How do I tell whether an invoice is fully paid?
Compare the sum of its payment rows with the invoice amount: =IF(SUMIFS(payments, invoice_col, invoice, ...) >= amount, "Paid", "Outstanding").
Why not use VLOOKUP on the payment log?
VLOOKUP returns the first matching row only, so a customer who paid in two instalments would look unpaid. Aggregating with SUMIFS is the only correct approach.