FormulaMath
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
=SUMIFS(Sales!D2:D500, Sales!A2:A500, ">="&G1, Sales!A2:A500, "<="&G2)
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, or the result is a #VALUE! error (Excel) or 0 (Sheets, silently — the worse failure).
- →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.