When you combine statements from several accounts, a transfer between your own accounts appears twice: once as a debit and once as a credit. If you count both, spending looks higher and income looks higher. Match each outgoing transfer to an incoming one of the same amount within a couple of days, and the pair should cancel to zero. Anything left over is a transfer that did not arrive, or one you mislabelled.
How do you set up the combined sheet?
Paste the rows from every account into one tab, one row per transaction, with these columns:
- A: date
- B: account name, for example Savings, Salary or Credit card
- C: description from the statement
- D: debit
- E: credit
- F: signed amount = ROUND(E2-D2,2), negative for money out
- G: type, which you set to Transfer by hand or with a filter on the description
Rounding in F avoids tiny paise mismatches when you compare amounts later.
To mark the transfers, filter the description column for words your banks use on own-account moves, such as your other account's name, the words self or own, or the card bill payment wording on your statement. Check the first few results by eye. A payment to a friend that happens to share a word with your own account name is exactly the kind of row that spoils the net-zero test.
What is the matching formula?
For a transfer row, look for a row in a different account with the opposite amount within two days either side. In H2, filled down:
=IF($G2<>"Transfer", "", IF(COUNTIFS($F$2:$F$300, -F2, $B$2:$B$300, "<>"&B2, $A$2:$A$300, ">="&A2-2, $A$2:$A$300, "<="&A2+2)=1, "Matched", IF(COUNTIFS($F$2:$F$300, -F2, $B$2:$B$300, "<>"&B2, $A$2:$A$300, ">="&A2-2, $A$2:$A$300, "<="&A2+2)=0, "No pair", "Several, check")))
The test reads: same amount with the sign flipped, a different account, a date within two days. One hit means Matched, none means No pair, and two or more means you have lookalike transfers and must check them yourself.
What does it look like with real rows?
Made-up example, five transfer rows across three accounts:
| Date | Account | Description | Signed amount | Status |
|---|---|---|---|---|
| 3 Nov | Savings | Transfer to salary account | -25,000 | Matched |
| 3 Nov | Salary | Transfer from savings | 25,000 | Matched |
| 10 Nov | Savings | Card bill payment | -10,000 | Matched |
| 12 Nov | Credit card | Payment received | 10,000 | Matched |
| 20 Nov | Savings | Transfer to wallet | -5,000 | No pair |
The card payment left the savings account on 10 November but was credited to the card on 12 November, which is why the window is two days rather than zero.
How do you prove the transfers net to zero?
Add a check cell:
=ROUND(SUMIFS($F$2:$F$300, $G$2:$G$300, "Transfer"), 2)
If every transfer had a partner, the answer is 0. In the example it is -₹5,000, which is exactly the unmatched wallet transfer: -25,000 + 25,000 - 10,000 + 10,000 - 5,000. A non-zero total is a useful alarm because it equals the sum of your problems.
What do you do with the totals afterwards?
Exclude rows marked Transfer from both your spending and income totals. Credit card bill payments are the classic trap: the card's purchases already count as spending, so the payment from savings should be treated as a transfer, not a second expense.
When to take another look
- Two identical transfers on the same day, which return Several.
- Transfers around month-end, where the credit lands in the next month's statement.
- Any No pair row older than a week.
In SpendixAI you pick the account before importing, and each transaction keeps that account, which gives you the account column this sheet needs without retyping it.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

