Same vendor, same amount: paid twice? (COUNTIFS)
The problem
Problem statement
Payments are in A2:C5. Put "Duplicate" or "Unique" for row 2 in the Flag cell D2, where duplicate means the same vendor was paid the same amount more than once.
Both vendor and amount must match — the same vendor at a different amount is two legitimate payments.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Vendor | Amount | Paid on | Flag |
| 2 | Acme | 1200 | 2026-02-03 | |
| 3 | Bolt | 900 | 2026-02-04 | |
| 4 | Acme | 1200 | 2026-02-10 | |
| 5 | Cobalt | 400 | 2026-02-11 |
Notes
A duplicate payment is a two-key match, so COUNTIFS takes two criteria pairs. Amounts are exact here; in real data you would compare rounded amounts, because a cent of FX drift hides the duplicate.
How to approach
Your answer goes in D2.
- Target cell is D2
- Two criteria pairs: vendor and amount
- Duplicate means the count is greater than 1
- Shape: =IF(COUNTIFS(?, ?, ?, ?)>1, ?, ?)
Function guide
FAQ
How do I find duplicate payments?
=IF(COUNTIFS(vendor_col, vendor, amount_col, amount)>1, "Duplicate", "Unique"), then filter and investigate. Round amounts first if they come from different currencies.
Why does a duplicate show as unique?
One of the two values differs by a rounding or a trailing space. Round the amounts or trim the vendor names before matching.