Spreadsheets

SUMIFS formula for monthly expenses in Excel (with examples)

Use SUMIFS in Excel to total expenses for each month: month-start cells, a less-than-next-month test, a 12-month table, EDATE and checks for text dates.

Editorial illustration for SUMIFS formula for monthly expenses in Excel (with examples)

To sum expenses by month in Excel, put the first day of each month in a cell and use SUMIFS with two date tests: on or after that day, and before the first day of the next month. With dates in A2:A201 and amounts in D2:D201, =SUMIFS($D$2:$D$201,$A$2:$A$201,">="&G2,$A$2:$A$201,"<"&EDATE(G2,1)) returns the month in G2.

Why use the first of the next month instead of the last day?

Because month lengths vary and some dates carry a time. A test of "<="&EOMONTH(G2,0) can miss a row stamped 31 March at 6 pm, since that is later than 31 March at midnight. Using "<"&EDATE(G2,1), which is the first moment of the next month, catches every moment of the month, with no special case for February or leap years.

How do I set up the month cells?

  1. In G2 type the first month: =DATE(2026,4,1) for April, if your financial year starts then.
  2. In G3 type =EDATE(G2,1) and copy it down to G13. You now have twelve month starts, April to March.
  3. Select G2:G13, open Format Cells and choose a custom format of mmm yyyy, so each cell reads Apr 2026 while still holding a real date.
  4. In H2 write the SUMIFS formula below and copy it down to H13.
  • =SUMIFS($D$2:$D$201,$A$2:$A$201,">="&G2,$A$2:$A$201,"<"&EDATE(G2,1))
  • SUMIFS takes the sum range first, then each pair of criteria range and criteria.

What does the arithmetic look like on a bank-statement sheet?

Imagine a sheet of 200 transaction rows for a year, with debits as positive numbers in column D. Say April's rows add up to ₹41,300, May's to ₹38,950 and June's to ₹44,200. The formula on April returns ₹41,300 only for dates from 1 April up to, but not including, 1 May. The first three months together are 41,300 + 38,950 + 44,200 = ₹1,24,450.

Month cellStarts on or afterBeforeResult in this example
Apr 20261 April 20261 May 2026₹41,300
May 20261 May 20261 June 2026₹38,950
Jun 20261 June 20261 July 2026₹44,200

How do I check that all twelve months add up?

Below the table, in H14, enter =SUM(H2:H13), and in H15 enter =SUM($D$2:$D$201)-H14. The second cell should be zero. If it is not, some dates fall outside the twelve months you listed, or some dates are text. Test the date column with =COUNT($A$2:$A$201). If the answer is less than the number of rows, some dates are not real dates, and SUMIFS will silently skip them.

Can I do the same for one particular month without a table?

Yes. Put any date from that month in a cell, say J1, and use ">="&DATE(YEAR(J1),MONTH(J1),1) for the start and "<"&EDATE(DATE(YEAR(J1),MONTH(J1),1),1) for the end. The formula is longer, but a single input then drives it. Change J1, and the answer follows.

What about a financial year that starts in April?

Nothing in the formula cares which month you start with. Change G2 to the first day of any month and the twelve rows follow. If you prefer to show the financial year as a label, put ="FY "&YEAR(G2)&"-"&RIGHT(YEAR(G2)+1,2) in a cell above the table, which reads FY 2026-27 for an April 2026 start.

A calendar-year view needs only one change: start G2 at 1 January. Because every month cell is built from the one above it, nothing else in the sheet has to be edited.

What else could I add?

  • A third pair of criteria for a category, so the same table can be split into Groceries and Rent.
  • A column that counts transactions with COUNTIFS and the same date tests.
  • A column that shows each month as a percentage of the total, using =H2/$H$14.

If keeping the sheet starts to feel like a monthly chore, SpendixAI can import the statements and give you monthly reports, so the sheet becomes optional.

SPENDIXAI JOURNAL

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

Explore your reports