Invoiced minus paid, per vendor (two SUMIFS)
The problem
Problem statement
Two tables, one report: invoices in A2:B4 and payments in D2:E6, where a vendor can pay in several instalments. For the vendor in A2, put the outstanding variance (invoiced minus paid) in the Variance cell F2.
Both sides must aggregate by vendor — a single payment row is not the paid amount.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | E | F | |
|---|---|---|---|---|---|
| 1 | Vendor | Invoiced | Vendor | Paid | Variance |
| 2 | Acme | 5000 | Acme | 4800 | |
| 3 | Bolt | 3200 | Acme | 200 | |
| 4 | Cobalt | 1800 | Bolt | 3200 | |
| 5 | Cobalt | 1000 | |||
| 6 | Cobalt | 500 |
Notes
The same SUMIFS pattern runs twice with different tables: sum the invoiced column by vendor, sum the paid column by vendor, subtract. Zero is a legitimate answer (fully paid), so do not wrap this in IF(x=0,"") — that would hide the vendors you are looking for.
How to approach
Your answer goes in F2. 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 F2
- Sum B2:B4 by A2's vendor, then sum E2:E6 by the same vendor
- Subtract the second from the first
- Shape: =SUMIFS(?, ?, ?) - SUMIFS(?, ?, ?)
Official solutions
Show 2 solutions — try it yourself first
Approach 1: two SUMIFS and a subtraction
=SUMIFS($B$2:$B$4, $A$2:$A$4, A2) - SUMIFS($E$2:$E$6, $D$2:$D$6, A2)
Acme invoiced 5000 and paid 4800 + 200 = 5000, so the variance is 0. When only part of an instalment has cleared, the same formula reports the difference instead of a status word.
Approach 2: SUMIF on both sides
=SUMIF($A$2:$A$4, A2, $B$2:$B$4) - SUMIF($D$2:$D$6, A2, $E$2:$E$6)
SUMIF keeps the criteria range first and the sum range last on each side. Identical result, and shorter to read when both conditions are a single equality.
Function guide
FAQ
How do I compare totals from two tables by key?
Aggregate each table by the key and subtract: =SUMIFS(invoiced, vendor_col, key) - SUMIFS(paid, vendor_col, key). Doing it with VLOOKUP only works if each table has one row per vendor.
Why does my variance show a dash or blank instead of 0?
Something is formatting zero as a dash, or the formula wrapped the result in IF(x=0,""). A zero variance is data, not an error.