Spreadsheets

Find missing months in a set of statements before you analyse them

Spot missing months in your statements with a COUNTIFS grid, a date-gap formula and a balance-chain check before incomplete history misleads your analysis.

Editorial illustration for Find missing months in a set of statements before you analyse them

List every month in your period in a column, count the transactions that fall in each with COUNTIFS, and look for zeros. Add a gap check on the sorted dates and confirm with the balance chain. A month with no rows is either a missing file or a genuinely quiet account, and you should know which before you analyse anything.

Why check for gaps first?

Averages and trends quietly assume every month is present. If July's statement never made it into the workbook, your yearly total is short, your monthly average is low, and nothing on the screen shows it. Spending twenty minutes on the checks below saves you from confident, wrong conclusions.

How do you build a month-by-month count?

  1. In A2 type the first day of your first month as a date, for example 1-Apr-2025. In A3 enter =EDATE(A2,1) and fill down to the last month.
  2. In B2 enter =COUNTIFS(Txns[Date],">="&A2,Txns[Date],"<"&EDATE(A2,1)) and fill down. If you are not using a Table, replace Txns[Date] with a range such as $A$2:$A$5000 from your data sheet.
  3. In C2 enter =IF(B2=0,"No rows","ok") and fill down.

Format column A as mmm-yyyy so it reads Apr-2025. For an April to March year, twelve rows cover it. The "<" with EDATE makes each month end just before the next begins, so no day is counted twice and none is missed.

What does a date-gap formula add?

A month with a few rows can still hide a missing fortnight. With your dates sorted oldest first in column A of the data sheet, put =A3-A2 in a helper column from the second row down. The result is the number of days between consecutive transactions. Then =MAX(B2:B2000) tells you the longest silence in the data. Decide in advance what is unusual for you. If you pay by UPI most days, a nine-day silence deserves a look; if you use the account only for rent, it may be normal.

Worked example with invented numbers: your grid shows counts of 138, 141, 0 and 129 for four consecutive months. The zero stands out at once. Before assuming the file is missing, look at the balance either side. If April closes at ₹40,000 and June opens at ₹40,000, the account really did nothing in May. If June opens at ₹36,500, May is missing, and so is ₹3,500 of movement.

CheckFormulaFlags
Rows per month=COUNTIFS(...) as aboveMonths with zero rows
Longest gap=MAX(B2:B2000)A silence longer than your habits explain
First date=MIN(Txns[Date])Later than the period you expect
Last date=MAX(Txns[Date])Earlier than the period you expect

How does the balance chain confirm a gap?

Take each month's opening balance and compare it with the previous month's closing balance. If they match, the months connect. If June opens lower or higher than May closed, something between them is not in the workbook. The balance chain finds missing days inside a month that a row count cannot, and the row count finds a missing month that a balance column you never filled would not.

What do you do about a missing month?

  • Download that period's statement and add it to the combined list.
  • If the bank does not provide it, note the gap and exclude those months from averages, instead of treating them as zero spending.
  • Mark the period as incomplete on your summary sheet.

Pre-analysis checklist

  • Every month has a row count above zero, or a reason.
  • First and last dates match the period you intend to analyse.
  • The longest gap is explained.
  • Balance chain runs unbroken from first to last statement.

SpendixAI keeps an import history with a status per upload, which helps you see what has actually been loaded.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports