How to Sum by Month in Excel

Method 1: SUMIFS with date boundaries

Assume transaction dates are in A and amounts are in B. Put a real first-of-month date in E2, such as 1/1/2026, and use =SUMIFS($B$2:$B$1000,$A$2:$A$1000,">="&E2,$A$2:$A$1000,"<"&EDATE(E2,1)). The formula includes the first instant of the month and stops before the first instant of the next month.

That exclusive upper boundary is safer than "<="&EOMONTH(E2,0) when source cells contain date-and-time values, because a timestamp late on the last day is later than midnight on that day and would be skipped. Copy the formula beside a sequence of monthly start dates and format those cells as mmmm yyyy for readable labels while the underlying date keeps its year.

When the data is an Excel Table named tblSales, structured references read more clearly and grow with the table: =SUMIFS(tblSales[Amount],tblSales[Date],">="&E2,tblSales[Date],"<"&EDATE(E2,1)). Build the month starts once, then fill the formula down a column of dates; each result stays tied to its own month label. A final check that the monthly totals add up to the grand total catches rows with missing or text dates.

Method 2: helper columns for inspection

A helper key can make filtering and auditing easier. =DATE(YEAR(A2),MONTH(A2),1) returns the first day of A2’s month. Fill it down, format it as mmm yyyy, and use SUMIF or a PivotTable against that field. Because the key remains a real date, January 2025 stays distinct from January 2026.

Avoid =MONTH(A2) alone unless the dataset truly contains one year: month number 1 combines every January. A text key such as =TEXT(A2,"yyyy-mm") is readable but is text, so sorting and charting differ from a date. Prefer the real-date key and control its display with number formatting.

Method 3: create a PivotTable

For an exploratory summary, select the clean source table and choose Insert > PivotTable. Put Date in Rows and Amount in Values. Recent versions often group dates automatically; if individual dates appear, right-click a date and choose Group, then select Months and Years. Make sure the value field says Sum of Amount rather than Count of Amount.

PivotTables are effective when users need filters and several dimensions. They must be refreshed after source changes, and appended records must be inside the source table or range. Compare the grand total with SUM of the source amount column before trusting the grouped output.

Common mistakes

Dates imported as text do not satisfy numeric date criteria, so SUMIFS returns 0; convert them first. A monthly label without a year combines periods, and an end-of-month comparison can miss values with times. The criteria ranges and the sum range must cover identical rows. Add more criteria as extra range-and-criterion pairs, for example region or status.