To find missing entries, put the bank statement and your expense log on two sheets, then use COUNTIFS to test whether each statement line has a row with the same date and amount in your log. Lines with a count of zero are missing from your log. Run the same test the other way round to find entries in your log that never reached the bank.
How should the two sheets be laid out?
Keep it simple. A sheet called Statement holds Date in column A, Narration in column B and Debit in column C, rows 2 to 500. A sheet called Mine holds Date in column A, Item in column B and Amount in column C. Both need real dates and real numbers. Leave the credit side aside for now and compare debits only.
Which formula marks the statement lines missing from your log?
In Statement!D2:
=IF(COUNTIFS(Mine!$A$2:$A$500, A2, Mine!$C$2:$C$500, C2)=0, "Missing in my log", "")
Fill down and filter on the text. Each flagged line is a payment the bank recorded that you did not.
And the reverse check?
In Mine!D2:
=IF(COUNTIFS(Statement!$A$2:$A$500, A2, Statement!$C$2:$C$500, C2)=0, "Not on statement", "")
Entries flagged here are either cash payments, which never touch the bank and are expected, or mistakes such as a wrong date or a typo in the amount, which you should fix.
What if the dates are a day or two apart?
Card and UPI payments sometimes post a day or two after you spent. Make the match tolerant by looking for the same amount within two days either side:
=IF(COUNTIFS(Mine!$A$2:$A$500, ">="&A2-2, Mine!$A$2:$A$500, "<="&A2+2, Mine!$C$2:$C$500, C2)=0, "Missing in my log", "")
The tolerance reduces false alarms but can hide a genuine miss if the same amount repeats within a few days, so use it as a second pass, not the first.
What does a small comparison show?
Say six statement debits are checked against your log:
| Statement line | Amount | In my log? | Result |
|---|---|---|---|
| Grocery payment | ₹1,240 | Yes | Matched |
| Electricity bill | ₹2,010 | Yes | Matched |
| Online order | ₹899 | No | Missing in my log |
| Fuel | ₹1,000 | Yes, dated 2 days earlier | Matched after tolerance |
| Cafe | ₹340 | Yes | Matched |
| Pharmacy | ₹470 | No | Missing in my log |
Two lines are missing, a total of ₹899 plus ₹470, which is ₹1,369. Add them and your monthly total moves up by exactly that amount. Use this as the proof: statement debits for the period minus your logged non-cash total should equal the entries you still have to add or explain.
How do you handle repeats on the same date?
COUNTIFS only tests whether at least one match exists. If you bought two ₹180 coffees on one day and logged only one, the first test still says matched. To catch that, compare counts. In Statement!E2:
=IF(COUNTIFS($A$2:$A$500, A2, $C$2:$C$500, C2) > COUNTIFS(Mine!$A$2:$A$500, A2, Mine!$C$2:$C$500, C2), "Count differs", "")
It flags every line where the statement has more rows for that date and amount than your log. In Excel 365 you could use XLOOKUP on a joined date and amount key instead, but the counting version works everywhere.
What is the routine to follow?
- Paste the statement dates and debits into the Statement sheet.
- Run the three flags and filter each one.
- Add missing items to your log, with the right category.
- Fix typos on the reverse list and leave genuine cash entries alone.
- Check that totals now agree for the period.
If matching by hand becomes a monthly chore, SpendixAI lets you upload a statement in PDF, CSV or Excel, review the rows before they are saved and add cash expenses by hand in batches, so your log starts from the statement rather than from memory.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

