Formulas containing dates and time in Excel can be frustrating if you don’t understand how they work.
And even if you do they seem to work differently from one formula to another!
A few weeks ago Dave wrote to me as he was having trouble getting a SUMIFS formula to correctly use dates referenced in its criteria.
For example let’s take the data below and say we want to sum the Sale Amount if the sales person is ‘Brady’, and the dates of the sales are between 1/1/2012 and 2/2/2012.
I’ve given the columns in my table the following named ranges:
- Sales Person = salesperson
- Sale Date = sales_date
- Sale Amount – sale_amt
I’ve set up my criteria at the top of my worksheet:
And I’ve given cells B5 and C5 range names:
- B5 = from_date
- B6 = to_date
I like to use named ranges as it makes building the formula easier and it’s more intuitive to read and interpret what the formula is doing.
SUMIFS Formula Using Date Criteria
In cell B6 I’ve put my SUMIFS formula:
Notice how the first date criterion is made up of text (surrounded by double quotes) then the ampersand, then a reference to a named range.
- Excel interprets the text ">=" as >=
- And the ampersand (&) tells Excel to join the text >= to the next part of the formula
Therefore the criterion ">="&from_date solves to read >=B5.
Likewise the criterion "<="&to_date solves to read <=B6.
Alternatively if you wanted to hard code the date criteria your formula would look like this:
Thanks for your question Dave.