Sum between two dates
Month-to-date revenue, spend in a quarter: SUMIFS with two date conditions — and the quoting trick that trips everyone the first time.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →SUMIFS takes the sum range FIRST, then any number of range/condition pairs — here the same date column twice, once with a floor and once with a ceiling, which is how "between" is spelled.
- →The ">="&G1 construction is the part people fight: the comparison operator must be text, and the cell reference is glued on with &. Writing ">=G1" literally compares against the text G1 and matches nothing.
The fine print
- →Both criteria ranges must be exactly the same size as the sum range — Excel returns #VALUE!, and Sheets returns "Array arguments to SUMIFS are of different size." Neither fails silently.
- →Dates stored as text don't compare as dates; if results look low, check the column with ISNUMBER first.
- →The bounds are inclusive on both ends — for a month, use >= the 1st and < the 1st of the NEXT month to dodge time-of-day tails.
Part by part
- {{date_range}}, ">="&{{start_date}}
- The operator as text concatenated to a cell reference. Writing ">=A1" compares against the literal text A1 and sums nothing.
- &
- Concatenation, and the part people omit. It is what turns a static comparison into one driven by a cell you can change.
- {{date_range}}, "<="&{{end_date}}
- The same range appearing twice with two conditions, which is how SUMIFS expresses a between. There is no range operator.
Common mistakes
=SUMIFS(B:B, A:A, ">=A1", A:A, "<=A2")Without the ampersand these compare against the literal text A1 and A2 rather than the dates in those cells. The formula returns zero and looks correct.
=SUMIFS(B:B, A:A, ">="&A1, A:A, "<="&A2)=SUMIFS(B:B, A:A, ">="&A1)One condition gives everything from the start date onward with no upper bound, so a report intended for one month silently includes every later month too.
Typing the dates directly as text.A date written as ">=01/03/2026" is interpreted by locale, so the same formula means March in one place and January in another. Reference a real date cell instead.
Questions
Why does my date range return zero?
Almost always a missing ampersand, or dates stored as text. Check the second: text dates align left by default while real dates align right, which is the fastest visual test.
Are the boundaries inclusive?
With >= and <=, yes — both endpoints are included. Use > and < if you want them excluded, and be deliberate about it in any month-end report where a day either way changes the total.
Why does a date-and-time value get excluded?
Because a timestamp on the end date is greater than the date alone, which is midnight. Compare against the end date plus one with a strict less-than, or the last day's entries silently drop out.
Can I add another condition?
Yes — SUMIFS takes further range and criteria pairs, all combined with AND. Every range must be the same size, or Excel returns #VALUE! rather than a partial result.