Conditional formula page
SUMIFS by Month Formula
Use this formula reference when you need the standard SUMIFS by month pattern for Excel or Google Sheets, with reusable month-cell, current-month, and extra-criteria variations.
Copy the standard SUMIFS by month formula pattern for Excel or Google Sheets.
If F1 contains any January 2026 date, this returns 730 for the January rows in the sample data.
Copy formulas
=SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)=SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)Excel and Google Sheets use the same DATE, EOMONTH, and SUMIFS month-boundary pattern. F1 can contain any real date inside the target month.
Example data
| Date | Region | Product | Amount | Rep |
|---|---|---|---|---|
| 2026-01-04 | East | Widget | 420 | Maya |
| 2026-01-12 | West | Widget | 310 | Noah |
| 2026-02-03 | East | Gadget | 275 | Maya |
| 2026-02-15 | East | Widget | 640 | Iris |
If F1 contains any January 2026 date, this returns 730 for the January rows in the sample data.
How the formula works
- DATE, YEAR, and MONTH build the first day of the target month.
- EOMONTH plus 1 builds the first day of the next month.
- The formula totals rows inside that boundary and avoids month-name text comparisons.
| Syntax piece | Role in the formula |
|---|---|
| DATE(YEAR(F1),MONTH(F1),1) | Builds the first calendar day of the month containing F1. |
| EOMONTH(F1,0)+1 | Builds the first day of the following month as an exclusive upper boundary. |
| D2:D100 | Totals Amount values whose Date falls between the two month boundaries. |
Verified examples
=SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)Excel: Enter any January 2026 date in F1. Returns: 730
=SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1, B2:B100, "East")Google Sheets: Enter any January 2026 date in F1 and restrict Region to East. Returns: 420
=SUMIFS(D2:D100, A2:A100, ">="&EOMONTH(TODAY(),-1)+1, A2:A100, "<"&EOMONTH(TODAY(),0)+1)Excel: No month input is required; TODAY supplies the current month. Returns: The total for rows in the current calendar month
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| The formula totals the right month in the wrong year | A MONTH-only comparison ignored YEAR and combined the same month across several years. | Keep the full first-day and next-month boundaries derived from F1. |
| Changing F1 does not change the total | F1 contains a month label stored as text rather than a real date. | Enter a complete date such as 2026-01-01 and format the cell as a month label if desired. |
| Rows on the last day are missing | The upper boundary used the month-end date at midnight while source cells contain times. | Keep the formula's <EOMONTH(F1,0)+1 boundary so the entire final day is included. |
When not to use this formula
- Use a pivot table when users need to group and compare many months interactively rather than calculate one selected month.
Alternatives
| Alternative | When to use it |
|---|---|
| SUMIFS Between Dates Formula | Use when the start and end dates are chosen independently rather than derived from one month cell. |
| Excel SUMIF by Month | Use for a broader explanation of why month reporting normally requires SUMIFS boundaries. |
Related formulas
Official references
- SUMIFS function from Microsoft
FAQ
Does F1 have to be the first day of the month?
No. YEAR, MONTH, DATE, and EOMONTH derive the boundaries from any real date inside the selected month.
Can the formula also filter by Region or Product?
Yes. Add another aligned criteria range and criterion pair after the two date conditions.