Spreadsheets

Average monthly expenses over the last 6 months in Excel

Find your average monthly expenses over the last 6 months in Excel by averaging monthly totals built with SUMIFS and EDATE, not averaging single transactions.

Editorial illustration for Average monthly expenses over the last 6 months in Excel

To get your average monthly expenses for the last six months in Excel, first total the spending for each month with SUMIFS and EDATE, then take the AVERAGE of those six totals. Do not average the raw transactions column: that gives you the typical size of one payment, not the typical size of a month.

Why does averaging the transactions give the wrong answer?

Imagine 160 transactions in six months that add up to ₹3,20,000. AVERAGE on the Amount column returns ₹2,000, which is the average payment. Your actual monthly spend is far higher because you make many payments each month. The number you want is one total per month, then the average of those totals.

How do you build the six monthly totals?

Assume a sheet called Log with Date in column A and Amount in column C, rows 2 to 1000. On a summary sheet, create a small helper table. In A2 put the first day of the month six months ago:

=EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1), -6)

Then in A3 put =EDATE(A2,1) and drag down to A7. That gives six month-start dates, ending with the last complete month. In B2 total each month:

=SUMIFS(Log!$C$2:$C$1000, Log!$A$2:$A$1000, ">="&A2, Log!$A$2:$A$1000, "<"&EDATE(A2,1))

Drag B2 down to B7. Format column A as mmm yyyy so you can read the months. The average is then one short cell:

=AVERAGE(B2:B7)

What does the arithmetic look like?

With made-up numbers, suppose the six monthly totals are as shown.

MonthTotal spent
Month 1₹52,000
Month 2₹48,500
Month 3₹61,200
Month 4₹55,300
Month 5₹47,000
Month 6₹56,000

The six totals add up to ₹3,20,000. Divided by 6, the average is ₹53,333 a month, rounded to the nearest rupee. Month 3 is the highest and Month 5 the lowest, a gap of ₹14,200. That spread is worth knowing as well as the average.

Is dividing one SUMIFS by 6 the same thing?

Often, yes. A single SUMIFS over the full six-month window divided by 6 gives the same result as the average of six totals, as long as every month has data. It differs when a month is blank, for instance when you started logging halfway through. Then AVERAGE would still count a zero month, and dividing by 6 would too. The month-count-aware version is:

=SUM(B2:B7)/COUNTIF(B2:B7, ">0")

This divides only by the months that actually have spending recorded. Pick this one if your oldest months are incomplete, and say so in a note next to the cell.

Should you leave anything out of the average?

You may want a second average that excludes one-off items, such as a big appliance or an annual premium, so that you can see your everyday rhythm. Add a column to the log, say column D with Regular or One-off, and use SUMIFS with an extra criterion of "Regular". Keep both averages visible, because planning needs the one-offs too.

One quick sanity check: multiply the average by 12 to see a rough yearly cost at your current habits. With the ₹53,333 average above, that is 53,333 times 12, or ₹6,39,996, close to ₹6.4 lakh. It is only a rough guide, since it ignores festivals, annual premiums and any change in your income.

What do you do with the number?

  • Compare it with your income to see the gap that is left each month.
  • Use it as a starting point for a budget, then adjust for the months that were unusual.
  • Re-check it every month; the window moves forward automatically because the formulas use TODAY().

Google Sheets accepts the same EDATE, SUMIFS and AVERAGE formulas, so the layout above works there too.

SPENDIXAI JOURNAL

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

Explore your reports