SUMIFS Date Range (Sum Values Between Two Dates Array)

Yes, you heard it right SUMIFS is the easiest and the best method to sum values between two dates (i.e. a range of dates).

Let me tell you something. Let’s say, you are working in a trading company and your boss asks you to get the total sales amount for the last 15 days.

In this case, you need to sum the sales amount from the current date to 15 days prior i.e. between two dates. Let me tell you how. Here, you have a range of two dates which includes a total of 15 days and you need to sum values for all of them.

And with SUMIFS you can add those two dates as criteria, and it works like a range and sum up all the values between that range.

Quick Intro: SUMIFS can help you to sum values by specifying more than one criterion. In short, it will only sum up those cells where all the conditions are met.

=SUMIFS(sum_range,range1,criteria1,[range2],[criteria2],…)

In the above syntax, first, you need to refer to the range from where you want to sum values, then the range to check for criteria, and after that criterion to check, and so on.

Formula to Sum Values Between Two Dates in Excel

Here you have sales data for the month of Jan, and you need to sum values between 15-Jan-2017 to 25-Jan-2017.

In the D2 cell, insert the formula below and hit enter:

=SUMIFS(B2:B31,A2:A31,">=15-Jan-2017",A2:A31,"<=25-Jan-2017")

When you hit enter, this function will return $15,682 in the cell which is the sum of the amount between 15-Jan-2017 to 25-Jan-2017. And here in this calculation, you have included both start and end dates as well.

To verify this calculation, just select amount cells from 15-Jan-2017 to 25-Jan-2017 and check out the status bar for the sum of these values or you can also filter values to check it.

Here’s How It Works.

In this formula, we have used logical operators with SUMIFS which helps to add a condition while calculating the sum. Let’s break down the formula to understand it.

• First, we have used range B2:B31 as a sum_range where we have the amount.
• After that, we have referred to A2:A31 as the first criteria range and “>=15-Jan-2017” as the first criterion.
• Next, we have again referred to A2:A31, but this time as the second criteria range and “<=25-jan-2017” as a second criterion.

So these two criteria tell the function to sum values that are equal to or greater than 15-Jan-2017 and which are equal to or lower than 25-Jan-2017.

So this point is, we have two different criteria, one for the lower limit and one for the upper limit, which creates a range of dates. And the function will only sum those values which are within this range.

Sum Values Between a Dynamic Range of Dates

There can be a situation when you need to sum values between two dates on an ongoing basis. Let’s say you need to sum values for the last 7 days and this should be dynamic so that every day you get the sum of the last 7 days. So the formulas will be:

=SUMIFS(B2:B31,A2:A31,"<="&TODAY(),A2:A31,">="&(TODAY()-6))

here’s how it Works

In the above formula, we have used the TODAY Function to get the current dynamic date which will change automatically. The first criterion tells the function to sum only those values which are equal to or lower than today’s date.

And, the second criterion tells the function to sum only those values which are equal to or lower than the date which is 6 days before today.

Now, the range of two dates will be something like this, from today to six days back which means you have a total of seven days. And the function will only sum values that are in this range.