Spreadsheets

EOMONTH with SUMIFS: expenses for any selected month

Use EOMONTH with SUMIFS to total expenses for any month you pick: one date cell, no typed month-end dates, and a fix for timestamps on the last day.

Editorial illustration for EOMONTH with SUMIFS: expenses for any selected month

Type any date from the month you care about into one cell, use EOMONTH to find the day before the month begins and the last day of the month, and pass both to SUMIFS. The result is the month's total for whichever date you pick, and it never needs a month-end typed by hand. One trap: if your dates carry a time of day, an expense late on the last day can fall outside a "<=" test, so use "<" with the next day instead.

What does EOMONTH actually return?

EOMONTH(date, months) returns the last day of the month that is that many months away from the date. With months set to 0 it gives the end of the same month; with -1 it gives the end of the previous month. Add 1 to the previous month's end and you have the first day of the selected month. So with 14/08/2025 in the cell:

  • =EOMONTH(E1,-1)+1 gives 01/08/2025, the first day.
  • =EOMONTH(E1,0) gives 31/08/2025, the last day.

Format both results as dates; otherwise you will see serial numbers like 45870, which is just how spreadsheets store dates.

How do I feed that into SUMIFS?

Assume the log has dates in A, categories in B and amounts in C, rows 2 to 500, and the picked date sits in E1. The one-cell version is:

=SUMIFS($C$2:$C$500,$A$2:$A$500,">="&(EOMONTH($E$1,-1)+1),$A$2:$A$500,"<"&(EOMONTH($E$1,0)+1))

The brackets matter: they make the date arithmetic finish before the text is joined. Everything on or after the first of the month and before the first of the next month is counted. For a single category, add a pair of arguments: $B$2:$B$500,"Groceries" placed straight after the sum range. Or point it at a cell holding the category name so you can copy the formula down a list.

If you prefer to see the working, put the first day in E2 and the last day in E3 using the two EOMONTH formulas above, then use ">="&$E$2 and "<"&$E$3+1. Same answer, easier to audit.

What is the timestamp trap on the last day?

Some exports store date and time together, such as 31/08/2025 18:45. A date with no time is midnight, so 31/08/2025 is 31/08/2025 00:00. The test "<="&EOMONTH(E1,0) means "up to midnight starting the 31st", which excludes the 18:45 entry. The month's total quietly comes up short, and only on the last day.

EntryAmountCounted by "<=" last dayCounted by "<" next day
01/08/2025 09:00₹4,200YesYes
15/08/2025 13:30₹2,800YesYes
31/08/2025 18:45₹1,250NoYes
01/09/2025 08:10₹900NoNo

With the "<=" test the August total is 4,200 + 2,800 = ₹7,000. With the "<" next-day test it is 4,200 + 2,800 + 1,250 = ₹8,250, which is right. The numbers are invented, but the gap of ₹1,250 is exactly what a missed last-day entry looks like in real life.

Can I pick the month from a drop-down instead?

Yes. Make E1 a date cell with a date validation (Data, then Data validation) so it only accepts dates. Anyone using the sheet types or picks any day of the month they want. If you would rather list month names, keep a small table with the first day of each month in a column and point E1 at it; the formulas stay identical because EOMONTH works on any date inside the month.

Why not use a month helper column instead?

A helper such as =TEXT(A2,"mmm yyyy") works, but it creates text you must match exactly, and a different regional setting can change month names or formats. The EOMONTH approach leaves the log untouched and compares real dates only. It also copes with Google Sheets as well as Excel, where EOMONTH and SUMIFS behave the same way.

Quick checklist

  • The picked cell holds a real date, not text.
  • Both results are formatted as dates while you test.
  • You are using "<" with the following day, not "<=" with the last day.
  • The sum range and the date range have the same number of rows.

If the log itself is the slow part, SpendixAI lets you upload statements as PDF, CSV or Excel, review rows before saving, and download a CSV to feed into a sheet like this one.

SPENDIXAI JOURNAL

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

Explore your reports