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 formula syntax for this pattern.
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 |
Formula explanation
- 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.
What this formula does
SUMIFS by Month Formula is built around SUMIFS. The example formula on this page is =SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1), and it is written against sample columns such as Date, Region, Product, Amount, Rep.
Excel and Google Sheets use the same copy pattern here, so the main work is adjusting ranges, criteria, and output cells to match your sheet.
Criteria formulas test each row before returning a total, count, or average. Every criteria range should cover the same row numbers as the result range so row-level logic stays aligned.
Use this pattern for this task: Copy the standard SUMIFS by month formula pattern for Excel or Google Sheets. Date and text criteria should be real spreadsheet values, not display-only text that only looks correct on screen.
| Syntax piece | Role in the formula |
|---|---|
| DATE | DATE, YEAR, and MONTH build the first day of the target month. |
| EOMONTH | EOMONTH plus 1 builds the first day of the next month. |
| Part 3 | The formula totals rows inside that boundary and avoids month-name text comparisons. |
Practical use cases
=SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)Excel: Use this in a sales, task, or reporting table where each row is tested before the final number is returned. Returns: Returns 730 when F1 is any January 2026 date in the sample data.
=SUMIFS(D2:D100, A2:A100, ">="&EOMONTH(TODAY(),-1)+1, A2:A100, "<"&EOMONTH(TODAY(),0)+1)Excel: Use this for dashboards where changing a criterion cell should refresh the result without rebuilding a pivot table. Returns: Totals rows in the current calendar month.
=SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1, B2:B100, "East")Google Sheets: Use this when the source table has stable columns and you need a repeatable result next to a monthly or regional summary. Returns: Returns 420 when F1 is any January 2026 date in the sample data.
Common errors
- F1 must contain a real date, not only the text January.
- The date range and sum range must cover the same rows.
- Use < next month instead of <= the last day when source values may contain times.
Common errors and troubleshooting
| Issue | Likely cause | Fix |
|---|---|---|
| Copied formula gives an unexpected result | F1 must contain a real date, not only the text January. | Resize the criteria ranges to the same rows, quote text criteria, and compare dates as real spreadsheet dates. |
| Common setup problem | The date range and sum range must cover the same rows. | Resize the criteria ranges to the same rows, quote text criteria, and compare dates as real spreadsheet dates. |
| Common setup problem | Use < next month instead of <= the last day when source values may contain times. | Resize the criteria ranges to the same rows, quote text criteria, and compare dates as real spreadsheet dates. |
| Formula returns 0 | The criteria text, date value, or numeric comparison does not match the source cells exactly. | Test the criterion against one row, then confirm all criteria ranges start and end on the same rows. |
| #VALUE! from criteria ranges | One criteria range is a different shape from the sum, count, or average range. | Use matching row counts such as A2:A100 with D2:D100 instead of mixing whole-column and partial-column references. |
When not to use this formula
- Do not use this reference page when you searched for SUMIF by month and need to understand why SUMIFS is usually required.
- Do not use SUMIFS when you need to ignore the year; use a SUMPRODUCT month comparison instead.
- Do not use this formula to return matching rows; use FILTER or QUERY for row output.
Alternatives
| Alternative | When to use it |
|---|---|
| Excel SUMIF by Month | Use when you searched for SUMIF and need the correction path to SUMIFS or SUMPRODUCT. |
| SUMIFS Formula Builder | Use when you want to build your own ranges and criteria. |
| SUMIFS by Month in Excel | Use for a worked example with sample data and expected result. |
Compare with related formulas
| Formula or tool | How it differs | Use it instead when |
|---|---|---|
| Excel SUMIF by Month | Excel SUMIF by Month solves a nearby workflow, while SUMIFS by Month Formula is focused on copy the standard sumifs by month formula pattern for excel or google sheets. | Use when you searched for SUMIF and need the correction path to SUMIFS or SUMPRODUCT. |
| SUMIFS Formula Builder | SUMIFS Formula Builder solves a nearby workflow, while SUMIFS by Month Formula is focused on copy the standard sumifs by month formula pattern for excel or google sheets. | Use when you want to build your own ranges and criteria. |
| SUMIFS by Month in Excel | SUMIFS by Month in Excel solves a nearby workflow, while SUMIFS by Month Formula is focused on copy the standard sumifs by month formula pattern for excel or google sheets. | Use for a worked example with sample data and expected result. |
Test cases
| Test | Platform | Formula | Returns |
|---|---|---|---|
| Standard month-cell SUMIFS | Excel | =SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1) | Returns 730 when F1 is any January 2026 date in the sample data. |
| Current month SUMIFS | Excel | =SUMIFS(D2:D100, A2:A100, ">="&EOMONTH(TODAY(),-1)+1, A2:A100, "<"&EOMONTH(TODAY(),0)+1) | Totals rows in the current calendar month. |
| SUMIFS by month plus region | Google Sheets | =SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1, B2:B100, "East") | Returns 420 when F1 is any January 2026 date in the sample data. |
Related formulas
FAQ
What does the SUMIFS by Month Formula do?
It supports this task: Copy the standard SUMIFS by month formula pattern for Excel or Google Sheets. The example uses D2:D100, but you should replace the ranges and criteria with the cells in your own workbook.
Does the SUMIFS by Month Formula work in Excel and Google Sheets?
Yes. The Excel and Google Sheets versions on this page use the same syntax for this pattern.
What should I change before copying the formula?
Change the source ranges, criteria cells, and output range references so they match your sheet. Keep related ranges on the same rows unless the formula notes say otherwise.
Why does this criteria formula return zero?
The criteria may not match the source cells exactly, or the criteria ranges may not align with the result range. Test one row and then confirm the full ranges.
Can I add another condition?
Use the IFS version of the formula when you need multiple independent criteria, such as region plus product plus date.
How should I handle dates in criteria formulas?
Use real date cells or date functions such as DATE and EOMONTH. Month names typed as text are easier to break.