Conditional formula page
SUMIFS Between Dates Formula
Use SUMIFS between dates when a report needs a total for rows on or after a start date and before the next boundary. The same formula works in Excel and Google Sheets when the date column contains real spreadsheet dates.
Total values between two dates with SUMIFS in Excel or Google Sheets.
If F1 is 2026-01-01 and G1 is 2026-02-01, this returns 730 for the January rows. February rows return 915 with 2026-02-01 to 2026-03-01, and East + January returns 420.
Copy formulas
=SUMIFS(D2:D100, A2:A100, ">="&F1, A2:A100, "<"&G1)=SUMIFS(D2:D100, A2:A100, ">="&F1, A2:A100, "<"&G1)The same SUMIFS between dates formula works in Excel and Google Sheets. Keep F1 and G1 as real dates, not typed text.
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
- The first date criterion includes rows on or after F1.
- The second date criterion stops before G1, so G1 should be the next day, next month, or next reporting boundary.
- The exclusive upper boundary avoids timestamp edge cases such as 2026-01-31 15:30.
- Add more criteria pairs after the date boundaries when the total also needs Region, Product, Status, or another condition.
What this formula does
SUMIFS Between Dates Formula is built around SUMIFS. The example formula on this page is =SUMIFS(D2:D100, A2:A100, ">="&F1, A2:A100, "<"&G1), 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: Total values between two dates with SUMIFS in 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 |
|---|---|
| F1 | The first date criterion includes rows on or after F1. |
| G1 | The second date criterion stops before G1, so G1 should be the next day, next month, or next reporting boundary. |
| Part 3 | The exclusive upper boundary avoids timestamp edge cases such as 2026-01-31 15:30. |
| DATE | Add more criteria pairs after the date boundaries when the total also needs Region, Product, Status, or another condition. |
Practical use cases
=SUMIFS(D2:D100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<"&DATE(2026,2,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 for the January rows: 420 plus 310.
=SUMIFS(D2:D100, A2:A100, ">="&F1, A2:A100, "<"&G1)Google Sheets: Use this for dashboards where changing a criterion cell should refresh the result without rebuilding a pivot table. Returns: Returns 915 when F1 is 2026-02-01 and G1 is 2026-03-01.
=SUMIFS(D2:D100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<"&DATE(2026,2,1), B2:B100, "East")Excel: 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 for East rows in January.
Common errors
- Date cells stored as text will not behave like real date serial values.
- Using <= endDate can miss rows when the source date column includes timestamps after midnight.
- The sum range and every criteria range must cover the same row set.
- A cell that only looks like a date may have the wrong format or text value underneath.
Common errors and troubleshooting
| Issue | Likely cause | Fix |
|---|---|---|
| Copied formula gives an unexpected result | Date cells stored as text will not behave like real date serial values. | Resize the criteria ranges to the same rows, quote text criteria, and compare dates as real spreadsheet dates. |
| Common setup problem | Using <= endDate can miss rows when the source date column includes timestamps after midnight. | Resize the criteria ranges to the same rows, quote text criteria, and compare dates as real spreadsheet dates. |
| Common setup problem | The sum range and every criteria range must cover the same row set. | 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 formula when you need returned rows instead of a total; use FILTER or QUERY.
- Do not use text month names as criteria when exact date boundaries are available.
Alternatives
| Alternative | When to use it |
|---|---|
| Excel SUMIF by Month | Use when a SUMIF by month search really needs SUMIFS month boundaries. |
| SUMIFS by Month Formula | Use for reusable month-cell and current-month SUMIFS patterns. |
| SUMIFS Formula Builder | Use when you want to build the ranges and criteria interactively. |
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 Between Dates Formula is focused on total values between two dates with sumifs in excel or google sheets. | Use when a SUMIF by month search really needs SUMIFS month boundaries. |
| SUMIFS by Month Formula | SUMIFS by Month Formula solves a nearby workflow, while SUMIFS Between Dates Formula is focused on total values between two dates with sumifs in excel or google sheets. | Use for reusable month-cell and current-month SUMIFS patterns. |
| SUMIFS Formula Builder | SUMIFS Formula Builder solves a nearby workflow, while SUMIFS Between Dates Formula is focused on total values between two dates with sumifs in excel or google sheets. | Use when you want to build the ranges and criteria interactively. |
Test cases
| Test | Platform | Formula | Returns |
|---|---|---|---|
| Fixed January dates | Excel | =SUMIFS(D2:D100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<"&DATE(2026,2,1)) | Returns 730 for the January rows: 420 plus 310. |
| Cell boundary dates | Google Sheets | =SUMIFS(D2:D100, A2:A100, ">="&F1, A2:A100, "<"&G1) | Returns 915 when F1 is 2026-02-01 and G1 is 2026-03-01. |
| Timestamp-safe month boundary plus region | Excel | =SUMIFS(D2:D100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<"&DATE(2026,2,1), B2:B100, "East") | Returns 420 for East rows in January. |
Related formulas
FAQ
What does the SUMIFS Between Dates Formula do?
It supports this task: Total values between two dates with SUMIFS in 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 Between Dates 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.