To find refunds that mirror an earlier purchase, use COUNTIFS on the same merchant, the opposite amount and a date window before the credit. If the count is above zero, the credit is probably a refund or reversal of that purchase, and you can net it against the original category so spending is not overstated.
Why does an unmatched refund matter?
Say you buy shoes for ₹1,299 in October and return them, with ₹1,299 back in the same month. If your category report adds only the debits, Shopping shows ₹1,299 even though you ended up spending nothing. The refund sits in a credit column nobody sums by category. Failed UPI payments that come back as reversals do the same, and they are easy to overlook.
How is the sheet set up?
- A: Date as a true date.
- B: Merchant, cleaned so the same shop has the same name each time.
- C: Signed amount, with debits negative and credits positive. Round amounts with =ROUND(...,2).
- D: Flag column for the result of the match.
The cleaned merchant name is the weak link. "SHOE STORE 123" and "Shoe Store Pvt" will not match. A helper column that strips store numbers and trailing text gives COUNTIFS something consistent to compare.
The COUNTIFS formula
In D2, for each row that is a credit, look back for a debit of the same size at the same merchant within 45 days:
- =IF(C2>0,IF(COUNTIFS($B$2:$B$500,B2,$C$2:$C$500,-C2,$A$2:$A$500,"<="&A2,$A$2:$A$500,">="&A2-45)>0,"Matches earlier purchase","Credit, check"),"")
- Criteria one: same merchant.
- Criteria two: amount equal to the refund with the sign flipped.
- Criteria three and four: dated on or before the refund, and no more than 45 days earlier.
- The 45 is only an example. Pick a window that fits how long your usual returns take.
| Date | Merchant | Amount | Result in D |
|---|---|---|---|
| 08-Oct | Shoe Store | -1,299 | blank, debit |
| 10-Oct | Fuel Pump | -2,000 | blank, debit |
| 21-Oct | Shoe Store | 1,299 | Matches earlier purchase |
| 23-Oct | Unknown Co | 750 | Credit, check |
How do you net the refund against the category?
Once matched, give the refund the same category as the purchase. In a Category column use a lookup, or simply type it for the handful of refunds you have. Then a category total built with SUMIFS across both debit and credit rows shows the net. Shopping becomes -1,299 + 1,299 = 0 rather than 1,299.
What about two identical purchases?
If you bought the same item twice at the same price, COUNTIFS says 2 and cannot tell which one the refund belongs to. For category totals it does not matter. If you need exact pairing, add a Pair ID column and type the same number on the purchase and its refund. Keep that for the few cases that need it.
Worked example with hypothetical figures
Say a month has Shopping purchases of 1,299, 3,450 and 899, and one matched refund of 1,299. Debits alone give 5,648. Net of the refund, 5,648 - 1,299 = 4,349. The first figure overstates what you spent by exactly the refunded amount. Check that both totals reconcile to the statement: debits 5,648, credits 1,299.
Which month should a refund count in?
Decide once and stick with it. If you track cash, a refund reduces spending in the month it arrives. If you want each month to show what you truly spent on that month's purchases, push the refund back to the month of the original purchase. A refund that arrives in November for an October purchase is the case where the two views differ, so label your sheet with the rule you chose.
Closing checklist
- Dates real, merchants cleaned, amounts rounded.
- COUNTIFS flag on every credit.
- Matched refunds take the purchase's category.
- Unmatched credits reviewed by hand.
SpendixAI also has a Possible duplicate charges report, which lists the same amount to the same merchant within two days; it is a different check but uses a similar idea.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


