How to sum by month in Excel
Formula (Excel and Google Sheets)
=SUMIFS($C$2:$C$11,$A$2:$A$11,">="&E2,$A$2:$A$11,"<="&EOMONTH(E2,0))List the first day of each month and use SUMIFS from that date to the month end: =SUMIFS($C$2:$C$11,$A$2:$A$11,">="&E2,$A$2:$A$11,"<="&EOMONTH(E2,0)).
- Result on the example
- one result per row in
F2, filled down toF4 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F4. Change any input and the result updates, or edit the formula to experiment.
How it works
Each month is a date window: on or after the first day in column E, and on or before EOMONTH(E2,0), the last day of the same month. The $ signs keep the data ranges fixed while E2 moves to E3 and E4 as you fill down. The rows dated January 31 and March 1 land in the right months.
=SUMIFS($C$2:$C$11,$A$2:$A$11,">="&E2,$A$2:$A$11,"<="&EOMONTH(E2,0))Result4400
$C$2:$C$1110 rows × 1 col: $1,250, $840, $2,310, $675, …A range: 10 cells in one column.
$A$2:$A$1110 rows × 1 col: 1/6/2026, 1/19/2026, 1/31/2026, 2/3/2026, …A range: 10 cells in one column.
E2Jan 2026The value in E2.
">="&E2">=46023"The & operator joins as text.
EOMONTH(E2,0)1/31/2026 (46053)EOMONTH: Last day of the month, months away.- start_date
E2Jan 2026 - months
0
- start_date
"<="&EOMONTH(E2,0)"<=46053"The & operator joins as text.
SUMIFS($C$2:$C$11,$A$2:$A$11,">="&E2,$A$2:$A$11,"<="&EOMONTH(E2,0))4400SUMIFS: Adds values that meet every condition.- sum_range
$C$2:$C$1110 rows × 1 col: $1,250, $840, $2,310, $675, … - criteria_range1
$A$2:$A$1110 rows × 1 col: 1/6/2026, 1/19/2026, 1/31/2026, 2/3/2026, … - criteria1
">="&E2">=46023" - criteria_range2
$A$2:$A$1110 rows × 1 col: 1/6/2026, 1/19/2026, 1/31/2026, 2/3/2026, … - criteria2
"<="&EOMONTH(E2,0)"<=46053"
- sum_range
In Google Sheets
The same formula works in Google Sheets: =SUMIFS($C$2:$C$11,$A$2:$A$11,">="&E2,$A$2:$A$11,"<="&EOMONTH(E2,0)). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((MONTH($A$2:$A$11)=MONTH(E2))*(YEAR($A$2:$A$11)=YEAR(E2))*$C$2:$C$11) | $4,400, $3,085, $4,625 | Compares month and year numbers instead of a date window. Slower on large data. |
Common mistakes
- Testing the month alone (
MONTH(A2:A11)=1) mixes January 2025 with January 2026. Test the year too, or use the date window. - If the dates include times,
"<="&EOMONTH(E2,0)stops at midnight at the start of the last day. Use"<"&EOMONTH(E2,0)+1to include the whole day. - Typing "Jan" in column E gives text, not a date. Enter the first of the month and format it to show the month name.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum by month
F2, total the amounts for the month that starts on the date in E2, then fill down to F4.Questions
How do I sum the current month?
Use TODAY for the window: =SUMIFS(C2:C11,A2:A11,">="&EOMONTH(TODAY(),-1)+1,A2:A11,"<="&EOMONTH(TODAY(),0)).
How do I list every month automatically?
In Excel 365 or Google Sheets, =EDATE(DATE(2026,1,1),SEQUENCE(12)-1) returns the first day of each month of 2026. In Google Sheets, wrap it in ARRAYFORMULA.
Can I use a PivotTable instead?
Yes. Put the date in Rows, group it by Months and Years, and put the amount in Values.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.