Loading both the checking account and the card into one list makes each credit card payment count twice. The checking account shows the payment leaving, the card shows the same amount arriving as a credit, and each of your purchases is already on the card. Only the purchases are spending. The payment and its matching credit should be tagged as a transfer and left out of every spending total.
Why does the payment show up in both places?
The bank pays the card from checking. In the checking export it is a withdrawal of, say, $1,240. In the card export it is a payment received for $1,240. These are two views of one movement of money between your own accounts. If you also list the purchases that made up the $1,240 on the card, the same dollars are in your sheet three times.
How do you flag the matching pairs?
Set up columns A date, B description, C account, D amount (money out negative, money in positive), and put this formula in E2 and fill down:
- =IF(COUNTIFS($D$2:$D$500, -D2, $C$2:$C$500, "<>"&C2, $A$2:$A$500, ">="&A2-3, $A$2:$A$500, "<="&A2+3)>0, "Possible transfer", "")
It marks a row when another row on a different account has the opposite amount within three days. That window allows for the card crediting a day or two after checking pays. It also catches transfers to savings, so review each flagged pair before you relabel it. Two unrelated purchases of the same size on the same days can be marked in error.
What does the fix look like with numbers?
| Row | Account | Amount | Tag |
|---|---|---|---|
| Payment out | Checking | -$1,240.00 | Transfer |
| Payment in | Card | +$1,240.00 | Transfer |
| Groceries | Card | -$310.00 | Food |
| Gas | Card | -$80.00 | Transport |
If your spending report reads transfers as spending, it counts $1,240 for the payment plus the purchases that created it. Excluding the Transfer tag takes it out: only the groceries and gas count, and the real bill is already in the purchases.
What should you check afterwards?
- Filter the sheet to the Transfer tag and total it. Out and in should cancel to roughly zero.
- Total all spending categories and compare with the money you know left your accounts.
- Search for round amounts that appear on two accounts and are not yet tagged.
- Look for card payments that were split into two parts, such as two payments in a month.
What causes false pairs and misses?
- Payments that clear more than three days apart, so the formula misses them.
- Refunds of the same amount as a purchase, which look like a pair.
- A partial payment, which leaves one side with no match.
- Different amounts because of a fee.
What about refunds and returns?
A refund on the card is a credit that can look like a payment. If you return a $60 item, the card shows +$60. Do not tag it as a transfer, because no money moved between your own accounts; it reverses a purchase. Give it the same category as the original purchase so that category's total falls by $60. Only a payment from your checking account is a transfer.
- Credit with a shop name: refund, same category.
- Credit with your bank name: payment, tag Transfer.
- Credit with no obvious source: check the date and amount before tagging.
- Fee refunds: record under fees, not income.
Where does an app help?
Mostly with the repeated work. SpendixAI is built for India and INR and is not intended for US accounts, but it keeps each imported transaction with the account it came from, which is the same fact that makes pairing possible in a sheet. When you pick a tool, look for one that keeps the account on every row.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


