How many vendors do not match (SUMPRODUCT over two columns)
The problem
Problem statement
Before closing the month, count the vendors where the paid amount in C2:C6 is not equal to the invoiced amount in B2:B6. Put the count in the Mismatched cell E2.
It must be one formula comparing the two columns row by row — COUNTIFS cannot compare one range against another.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | E | |
|---|---|---|---|---|
| 1 | Vendor | Invoiced | Paid | Mismatched |
| 2 | Acme | 5000 | 5000 | |
| 3 | Bolt | 3200 | 3000 | |
| 4 | Cobalt | 1800 | 1800 | |
| 5 | Dana | 900 | 950 | |
| 6 | Eli | 400 | 0 |
Notes
SUMPRODUCT(--(B2:B6<>C2:C6)) compares the two columns row by row, turns each difference into 1, and adds them. This is the one job COUNTIFS cannot do: its criteria are constants, so it cannot say "where column B is not equal to column C". A paid amount of 0 counts as a mismatch, which is intended — nobody paid.
How to approach
Your answer goes in E2. 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 E2
- Compare the two columns row by row, not column to column
- The <> operator gives TRUE for rows that differ
- Shape: =SUMPRODUCT(--(? <> ?))
Official solutions
Show 2 solutions — try it yourself first
Approach 1: count the differing rows directly
=SUMPRODUCT(--($B$2:$B$6 <> $C$2:$C$6))
Bolt (3200 vs 3000), Dana (900 vs 950) and Eli (400 vs 0) differ, so the answer is 3. Acme and Cobalt balance out and contribute 0.
Approach 2: total rows minus the matching rows
=ROWS($B$2:$B$6) - SUMPRODUCT(--($B$2:$B$6 = $C$2:$C$6))
Five rows minus the two that match leaves the three that do not. Same answer, and sometimes easier to sanity-check when you also want to report the match count.
FAQ
How do I compare two columns row by row in Excel?
=SUMPRODUCT(--(A2:A10<>B2:B10)) counts the rows that differ. COUNTIFS cannot do it because its criteria must be constants.
Does a blank or zero paid amount count as a mismatch?
Yes — it is not equal to the invoiced amount. That is usually what you want at month end: nobody paid is the worst mismatch.