Conditional formula page

SUMIF / SUMIFS Between Dates Formula

Q: Should the end date use <=G1 or <G1+1?

Use <=G1 only for date-only source cells. Use <G1+1 when G1 is inclusive and imported source values may include times.

Q: Why does the basic formula use the next period in G1?

Using the first excluded date makes the upper boundary unambiguous and includes every time value before that boundary.

Direct answer: use SUMIFS, not a single SUMIF, when you need a total between two dates. SUMIFS lets you apply a start boundary and an end boundary to the same date column in Excel or Google Sheets.

Best for

Total values between two dates with SUMIF or SUMIFS in Excel or Google Sheets.

What it returns

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

Excel formula

=SUMIFS(D2:D100, A2:A100, ">="&F1, A2:A100, "<"&G1)

Google Sheets formula

=SUMIFS(D2:D100, A2:A100, ">="&F1, A2:A100, "<"&G1)

Excel / Google Sheets difference

Excel and Google Sheets use the same SUMIFS date-criteria syntax. An exclusive upper boundary is safest when imported dates may include time values.

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

What it returns

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.

How the formula works

A single SUMIF can test one date condition, but a date range needs two conditions, so use SUMIFS.
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.
Use <= endDate only when the source column contains pure date values with no time portion. Use < endDate+1 when imported data may contain timestamps.
In Google Sheets, the same SUMIFS logic works, but date cells should be real serial date values rather than typed text produced by imports.
Add more criteria pairs after the date boundaries when the total also needs Region, Product, Status, or another condition.

Syntax piece	Role in the formula
D2:D100	The Amount values to total for dates inside the selected window.
>=&F1	Includes rows on or after the start date stored in F1.
<&G1	Stops before G1, which should contain the next day or next reporting-period boundary.

Verified examples

January with an exclusive upper boundary

=SUMIFS(D2:D100, A2:A100, ">="&F1, A2:A100, "<"&G1)

Excel: Enter 2026-01-01 in F1 and 2026-02-01 in G1. Returns: 730

Inclusive end-date cell with timestamps

=SUMIFS(D2:D100, A2:A100, ">="&F1, A2:A100, "<"&(G1+1))

Google Sheets: Enter 2026-01-01 in F1 and 2026-01-31 in G1; source dates may contain times. Returns: 730

East transactions in January

=SUMIFS(D2:D100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<"&DATE(2026,2,1), B2:B100, "East")

Excel: Use fixed January boundaries and add East as the Region criterion. Returns: 420

Common errors and fixes

Issue	Likely cause	Fix
Transactions late on the end date are missing	The formula uses <=G1 while source cells include times and G1 represents midnight.	Use <G1+1 for an inclusive end-date cell, or store the first excluded date in G1 and use <G1.
A visible date range returns zero	F1, G1, or the Date column contains text rather than spreadsheet date serials.	Convert all three inputs to real dates and test one row with a direct comparison.
Adding Region causes #VALUE!	The added Region criteria range covers different rows than the Date and Amount ranges.	Use matching boundaries such as A2:A100, B2:B100, and D2:D100.

When not to use this formula

Use FILTER or QUERY when the output should show each matching transaction instead of one total.

Alternatives

Alternative	When to use it
SUMIFS by Month Formula	Use when one selected date should determine the entire calendar month automatically.
COUNTIFS Between Dates Formula	Use when the result should be the number of rows in the date window.

Related formulas

Official references

SUMIFS function from Microsoft

FAQ

Should the end date use <=G1 or <G1+1?

Use <=G1 only for date-only source cells. Use <G1+1 when G1 is inclusive and imported source values may include times.

Why does the basic formula use the next period in G1?

Using the first excluded date makes the upper boundary unambiguous and includes every time value before that boundary.