How to sum by month in Excel

Step 1: a list of months

Use real dates for the months, not text like "Aug". Type the first day of the first month in F2, then add one month at a time with EDATE. Format the cells to show just the month and year, so 8/1/2026 displays as Aug 2026 but stays a date that formulas can compare.

1. In F2, type the first day of the first month, for example 8/1/2026 (or 1/8/2026 if your dates are day-first).
2. In F3, type =EDATE(F2,1) and fill it down for as many months as you need.
3. Select F2:F4, press Ctrl+1, choose Custom and type mmm yyyy.

Step 2: SUMIFS between the 1st and the next 1st

SUMIFS adds the amounts in D whose date is on or after the month start and before the next month's start. EOMONTH(F2,0) is the last day of the month, so adding 1 gives the first day of the next month.

The data ranges are locked with $, and F2 is not. When you fill G2 down to G4, the ranges stay put and F2 becomes F3 and F4, so each row totals its own month. September gives 3,840 and October 3,705.

Why "<" the next month instead of "<=" the last day

Dates that come from a system export often carry a time, such as 8/31/2026 4:45 PM. That is a little more than 8/31/2026, so a "<="&EOMONTH(F2,0) test leaves it out, and the last afternoon of every month quietly disappears. "Less than the next month's first day" includes the whole last day, whatever the time.

If your month cell can be any day in the month rather than the 1st, start from EOMONTH(F2,-1)+1 instead of F2. That is the first day of the same month.

The MONTH() shortcut and its two traps

You will often see =SUMPRODUCT((MONTH(A2:A11)=9)*D2:D11). It looks simpler, but MONTH only knows the month number. Here it adds the September 2025 sale to September 2026.

The second trap is the walk-in sale in row 11 with no date. An empty cell is day 0 to Excel, and MONTH of 0 is 1, so =SUMPRODUCT((MONTH(A2:A11)=1)*D2:D11) puts that 150 into January. If you use this pattern, always add YEAR: =SUMPRODUCT((YEAR(A2:A11)=2026)*(MONTH(A2:A11)=9)*D2:D11) gives 3,840.

Sum by month and category

Add more range and criteria pairs to the same SUMIFS. For a summary grid, put the categories across row 1 and the months down column F, and lock the references so one formula fills the whole grid: the category header with a $ before the row number (G$1), the month with a $ before the column letter ($F2).

A blank date never falls between two real dates, so SUMIFS leaves the undated walk-in sale out of every month. Check for those rows separately: =SUMIFS(D2:D11,A2:A11,"") finds 150 that is in no month.

No formulas: a PivotTable grouped by month

For a quick report you will not maintain by hand, a PivotTable is faster. In Excel 2016 and later, dragging a date field into Rows usually groups it automatically. If it does not, group it yourself. Always group by Months and Years together, or every September from every year lands in one row.

If Group is greyed out or says "Cannot group that selection", the date column contains text or blanks. Fix the text dates first, or filter out the blank row.

1. Click any cell in the data and choose Insert > PivotTable > OK.
2. Drag Date to Rows and Amount to Values.
3. If the dates are not grouped, right-click a date in the PivotTable > Group.
4. Select Months and Years, then OK.
5. When you add new rows, right-click the PivotTable > Refresh. Format the data as a table first (Ctrl+T) so new rows are included.