Excel formula workflow
Excel SUMIF by Month
If you searched for an Excel SUMIF date month formula, the working answer is usually SUMIFS with a start-of-month and next-month boundary. The month-boundary SUMIFS pattern works in both Excel and Google Sheets, and this page shows why MONTH(range) fails inside SUMIFS, what to use for the current month, and when the ignore-year SUMPRODUCT pattern is appropriate.
SUMIF usually cannot handle month boundaries by itself. Use SUMIFS for one month in one year, or SUMPRODUCT only when you intentionally want to ignore the year.
Copy-paste formulas
=SUMIFS(D2:D100,A2:A100,">="&DATE(YEAR(F1),MONTH(F1),1),A2:A100,"<"&EOMONTH(F1,0)+1)With the sample data, this returns 730 when F1 is any January 2026 date.
=SUMIFS(D2:D100,A2:A100,">="&EOMONTH(TODAY(),-1)+1,A2:A100,"<"&EOMONTH(TODAY(),0)+1)This updates automatically during the current month.
=SUMPRODUCT((MONTH(A2:A100)=MONTH(F1))*D2:D100)Use this only when January across all years should be combined.
Date boundaries for one month
Use an inclusive start boundary and an exclusive next-month boundary.
For January 2026, the logic is Date >= 2026-01-01 and Date < 2026-02-01. This includes every January row, including dates with time values such as 2026-01-31 15:30.
If the user enters the last visible day of the month instead of the next-month date, add one day to the end date and still use the < operator.
=SUMIFS(D2:D100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1))Returns 730 with the sample data.
=SUMIFS(D2:D100,A2:A100,">="&F1,A2:A100,"<"&(G1+1))Use this when F1 is the first date to include and G1 is the last visible date to include.
Month boundary check
For monthly totals, the upper boundary should usually be the first day of the next month, not the last displayed day of the current month.
If you need returned rows instead of one monthly total, use a FILTER date example or a Google Sheets QUERY date range instead of SUMIFS.
=SUMIFS(D2:D100,A2:A100,">="&DATE(YEAR(F1),MONTH(F1),1),A2:A100,"<"&EOMONTH(F1,0)+1)Works in Excel and Google Sheets when F1 is a real date from the target month.
Core SUMIFS month pattern
Use SUMIFS with two date criteria: one for the first day of the selected month and one for the first day of the next month.
=SUMIFS(D2:D100,A2:A100,">="&DATE(YEAR(F1),MONTH(F1),1),A2:A100,"<"&EOMONTH(F1,0)+1)This is the standard Excel month-cell pattern.
Why SUMIF by month usually becomes SUMIFS
SUMIF has one criteria pair. A month needs two date boundaries: on or after the first day and before the first day of the next month.
SUMIFS supports both criteria against the same date column, which makes it safer than trying to force MONTH(range) inside SUMIF.
Excel and Google Sheets support
The month-boundary SUMIFS formula works in both Excel and Google Sheets when the date column contains real dates.
Use the same >= month start and < next month criteria in either app. Keep the target month in a real date cell, then format that cell as a month name if you only want to display January or February.
In Google Sheets, avoid typed month text such as January as the criterion. Put a real date in F1, use DATE or EOMONTH, and let the cell format display the month name.
=SUMIFS(D2:D100,A2:A100,">="&DATE(YEAR(F1),MONTH(F1),1),A2:A100,"<"&EOMONTH(F1,0)+1)Same syntax as Excel when A2:A100 contains real dates.
Sum one month from a date cell
Put any real date from the target month in F1. The formula calculates the first day and next-month boundary from that date.
=SUMIFS(D2:D100,A2:A100,">="&DATE(YEAR(F1),MONTH(F1),1),A2:A100,"<"&EOMONTH(F1,0)+1)Sum the current month
Use TODAY with EOMONTH when the report should always show the current calendar month.
=SUMIFS(D2:D100,A2:A100,">="&EOMONTH(TODAY(),-1)+1,A2:A100,"<"&EOMONTH(TODAY(),0)+1)Sum by month and another criterion
Add another criteria pair for region, product, owner, status, or category.
=SUMIFS(D2:D100,A2:A100,">="&DATE(YEAR(F1),MONTH(F1),1),A2:A100,"<"&EOMONTH(F1,0)+1,B2:B100,"East")Ignore the year with SUMPRODUCT
SUMPRODUCT can sum every January across all years, but it is not the default monthly report formula because it ignores the year by design.
Use it only when combining the same month across different years is the actual requirement.
Use full-column SUMPRODUCT formulas carefully because they can recalculate more work than SUMIFS.
=SUMPRODUCT((MONTH(A2:A100)=MONTH(F1))*D2:D100)Why MONTH(range) fails inside SUMIFS
A formula such as =SUMIFS(D2:D100,MONTH(A2:A100),1) fails because SUMIFS criteria_range arguments must be real ranges, not arrays returned by MONTH.
Fix it by replacing MONTH(range) with two real date criteria: A2:A100 >= the first day of the month and A2:A100 < the first day of the next month.
If you truly need array logic such as ignoring the year, switch to SUMPRODUCT and make that choice explicit.
=SUMIFS(D2:D100,MONTH(A2:A100),MONTH(F1))MONTH(A2:A100) is an array expression, not a criteria range.
=SUMIFS(D2:D100,A2:A100,">="&DATE(YEAR(F1),MONTH(F1),1),A2:A100,"<"&EOMONTH(F1,0)+1)Works in Excel and Google Sheets.
Troubleshoot zero or missing monthly totals
If the result is 0, first test whether the dates are real spreadsheet dates. A value can look like 2026-01-04 and still be stored as text after a CSV import.
Then check that the sum range and both date criteria ranges cover the same rows. Mixed ranges such as D2:D100 with A:A can create unreliable results.
If a visible month-end row is missing, replace <= end of month with < first day of next month, or use < endDate+1 when G1 contains the last day to include.
Common errors
MONTH(range) inside SUMIFS does not create a criteria range. SUMIFS criteria need actual range and criteria pairs.
Text such as January is not the same as a real date. Use a real date cell and format it to display the month name.
<= end of month can miss timestamp edge cases; < next month is safer.
SUMIF is usually too limited when month boundaries need two criteria.
SUMPRODUCT can ignore year, but it is not the default month-reporting formula.
When not to use this formula
Do not use this formula when you need returned rows instead of a total. Use FILTER or QUERY for row output.
Do not use SUMPRODUCT to ignore the year unless combining the same month across different years is intentional.
Sample data
| Date | Region | Product | Amount |
|---|---|---|---|
| 2026-01-04 | East | Widget | 420 |
| 2026-01-12 | West | Widget | 310 |
| 2026-02-03 | East | Gadget | 275 |
| 2026-02-15 | East | Widget | 640 |
Returned totals from the sample data
| Pattern | Returned result | Rows included |
|---|---|---|
| SUMIFS month-cell pattern with F1 in January 2026 | 730 | The two January rows: 420 and 310. |
| SUMIFS month-cell pattern with F1 in February 2026 | 915 | The two February rows: 275 and 640. |
| Fixed January 2026 date boundaries | 730 | Rows on or after 2026-01-01 and before 2026-02-01. |
| Month plus Region = East for January 2026 | 420 | Only the 2026-01-04 East row. |
| SUMPRODUCT ignore-year pattern with F1 in January | 730 in this one-year sample | Use only when combining the same month across different years is intentional. |
Which month formula should you use?
| Need | Formula pattern | Why |
|---|---|---|
| One month in one year | SUMIFS with >= first day and < next month | Best default for monthly reports. |
| Current calendar month | SUMIFS with EOMONTH(TODAY(),-1)+1 and EOMONTH(TODAY(),0)+1 | Updates automatically as the month changes. |
| Same month across all years | SUMPRODUCT with MONTH(date_range)=MONTH(F1) | Intentionally ignores the year. |
| Rows, not a total | FILTER or QUERY | Returns matching records instead of one number. |
Do not use this when
- MONTH(range) inside SUMIFS does not create a criteria range.
- Text month names are not the same as real dates.
- < next month is safer than <= the last day when source values may contain times.
- SUMPRODUCT ignores the year in the shown pattern, so do not use it as the default monthly report formula.
Related tools and guides
FAQ
Can SUMIF sum dates by month?
SUMIF can match one criterion, but a month usually needs two date boundaries. SUMIFS is the safer answer.
Why does MONTH(range) fail inside SUMIFS?
SUMIFS expects criteria ranges, not arrays created by MONTH(range). Use real date boundaries or SUMPRODUCT for array logic.
How do I sum the current month in Excel?
Use EOMONTH(TODAY(),-1)+1 for the first day of this month and EOMONTH(TODAY(),0)+1 for the first day of next month.
How do I sum January across all years?
Use a SUMPRODUCT month comparison, such as SUMPRODUCT((MONTH(A2:A100)=MONTH(F1))*D2:D100).
Does this formula work in Google Sheets?
Yes. The SUMIFS month-boundary formula and the SUMPRODUCT pattern also work in Google Sheets.
Should I use SUMIF, SUMIFS, or SUMPRODUCT?
Use SUMIFS for one month in one year, SUMPRODUCT when you intentionally ignore the year, and SUMIF only for one simple condition.