SUMIF with a Date Range
SUMIF only takes one condition, so summing between two dates needs a trick. Learn three clean ways to SUMIF (or SUMIFS) over a date range in Excel and Google Sheets.
Introduction
A very common question is "how do I SUMIF the amounts where the date is between Jan 1 and Mar 31?" Because SUMIF accepts a single criterion, a single date range isn't enough. This guide shows three patterns — two SUMIFs, the modern SUMIFS, and a SUMPRODUCT fallback — so you can total by any date window.
Prerequisites
- Dates stored as real Excel dates (not text)
- A column of dates and a column of values to sum
- Microsoft 365 only needed for dynamic array tricks
1The Problem with One SUMIF
SUMIF(range, criteria, sum_range) compares the whole range to a single test. To capture a window you need BOTH a lower bound and an upper bound, which is two criteria — so a single SUMIF can't do it alone.
Confirm your dates are real serial numbers: a date formatted as text won't compare correctly.
2Method 1: Two SUMIFs (Subtract the Upper Bound)
Sum everything from the start date, then subtract everything after the end date. The difference is the window. This works in every version.
Sum from start
Include all dates on or after the start.
=SUMIF(A:A, ">="&DATE(2024,1,1), B:B)Subtract after end
Remove dates after the window's last day.
=SUMIF(A:A, ">"&DATE(2024,3,31), B:B)Example
=SUMIF(A:A, ">="&DATE(2024,1,1), B:B) - SUMIF(A:A, ">"&DATE(2024,3,31), B:B)The second SUMIF excludes the end day, so use 31 Mar as the cutoff; rows on 31 Mar are kept in the first sum and removed by the second.
Use ">=" for the start and ">" (strictly greater) for the end so the last day is included.
3Method 2: SUMIFS (Cleanest, Excel 2007+/Sheets)
SUMIFS handles multiple criteria natively, so the same lower/upper pair becomes two simple conditions in one function.
Example
=SUMIFS(B:B, A:A, ">="&DATE(2024,1,1), A:A, "<="&DATE(2024,3,31))Both bounds are inclusive here, which is usually what a report wants.
SUMIFS is generally faster and easier to read than the two-SUMIF trick.
4Method 3: SUMPRODUCT (Maximum Control)
SUMPRODUCT multiplies arrays, so you can express the date window as TRUE/FALSE math and even combine it with other conditions.
Example
=SUMPRODUCT((A:A>=DATE(2024,1,1))*(A:A<=DATE(2024,3,31))*B:B)Each condition returns 1/0; only rows where both are 1 contribute their B value.
Restrict to your data range (A2:A1000) instead of whole columns to keep SUMPRODUCT fast.
Functions Used
Summary
To SUMIF over a date range, remember that one criterion isn't enough. The two-SUMIF subtraction works everywhere, SUMIFS is the cleanest modern choice, and SUMPRODUCT gives you the most flexibility when you start mixing in extra conditions. Prefer SUMIFS for new work.
Next Steps
- Count rows in the same window with COUNTIFS
- Switch the DATE() bounds for cell references so the report is reusable
- Combine the date window with a category condition using SUMIFS