If your debit or credit total looks far too big, check whether the opening or closing balance line is sitting in the same column as the transactions. Add a Type column that labels those lines "Opening" or "Closing" and every other row "Txn", then total only the Txn rows and keep the balance lines for checking your work.
Why do balance lines inflate the totals?
In many exports, the first and last rows are not transactions at all. They are labelled something like opening balance, brought forward or closing balance, and the figure can land in the same column as your payments. A SUM over that column then adds a balance of tens of thousands to a month of small payments. Say a month has debits of ₹12,000 and ₹8,500, a real total of ₹20,500, but a closing balance line of ₹39,500 sits in the debit column: the plain total reads 12,000 + 8,500 + 39,500 = ₹60,000. The sheet looks fine, the answer is wrong by almost three times, and nothing flags it.
How do you tag the balance rows?
Take columns A for date, B narration, C debit, D credit and E for the new Type column. In E2 enter:
=IF(ISNUMBER(SEARCH("opening balance",B2)),"Opening",IF(ISNUMBER(SEARCH("closing balance",B2)),"Closing","Txn"))
Fill it down. SEARCH ignores capitals, so it matches however the line is printed. Your statement may use other words, such as brought forward or carried forward. Read the first and last rows of your own file and add an extra IF for each wording you find, or extend the test with OR(...) so one formula covers them. If you have lots of wordings, move them into a list and use SUMPRODUCT with SEARCH, the way a keyword list works for categories.
Test the Type column before using it: filter it for anything other than Txn and confirm that only the first and last rows come up. If a middle row is tagged Opening or Closing, a merchant's name probably contains the word, and the keyword needs to be made more specific.
How do you total only the real transactions?
- Debits: =SUMIFS($C$2:$C$500,$E$2:$E$500,"Txn")
- Credits: =SUMIFS($D$2:$D$500,$E$2:$E$500,"Txn")
- Number of real rows: =COUNTIFS($E$2:$E$500,"Txn")
Do not delete the balance lines. Tag them and leave them in, because they are what lets you test whether the sheet is complete.
How do you use the balance lines to reconcile?
The rule is opening balance plus credits minus debits equals closing balance. With the Type column in place, pull the two figures out regardless of which column they landed in:
Opening: =SUMPRODUCT(($E$2:$E$500="Opening")*$C$2:$D$500)
Closing: =SUMPRODUCT(($E$2:$E$500="Closing")*$C$2:$D$500)
Difference: =Opening + SUMIFS($D$2:$D$500,$E$2:$E$500,"Txn") - SUMIFS($C$2:$C$500,$E$2:$E$500,"Txn") - Closing
In the invented example, opening ₹45,000 plus credits of ₹15,000 minus debits of ₹20,500 gives ₹39,500, which equals the closing figure, so the difference is 0. A non-zero difference means a missing row, a duplicate, or a transaction mis-tagged as a balance line. Round the check to two decimals if paise are involved, for example with ROUND(...,2).
What if the balance sits in its own Balance column?
Then it may not be inflating anything, and the Type column is optional. You still gain from tagging the first and last rows, since a running-balance column makes the opening figure easy to confuse with a payment on the first day. In that case, use the Balance column of the Closing row as your check value directly, and leave the debit and credit sums alone.
Before you move on
- Look at the first and last five rows of every statement file.
- Add Type, fill down, and filter for anything not Txn to see what was caught.
- Compare the plain SUM with the SUMIFS total; the gap should equal your balance lines.
- Run the reconciliation and confirm the difference is zero.
- Only then start categorising.
SpendixAI lets you review the rows from an uploaded statement before saving them, which is a handy moment to spot a balance line that slipped in.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

