SUMIFS Date Range (Sum Values Between Two Dates Array)

Yes, you heard it right.

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

Let me tell you something. Suppose you work in a trading company, and your boss asks you to calculate the total sales amount for the last 15 days.

In this case, you can 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, including 15 days, and you need to sum the values for all.

With SUMIFS, you can add those two dates as criteria. It works like a range and sums up all the values within that range.

Quick Intro: SUMIFS can help you sum values by specifying multiple criteria. 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, the criterion to check, and so on.

Formula to Sum Values Between Two Dates in Excel

Here, you have sales data for January, and you need to sum the values between 15-Jan-2017 (start date) and 25-Jan-2017 (end date).

raw-data.xlsx

Now copy the formula that we have mentioned below and then paste it into cell D2.

And then 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, the sum between 15-Jan-2017 (start date) and 25-Jan-2017 (end date).

And here, in this calculation, you have included both start and end dates.

To verify this calculation, select the amount cells from 15 January 2017 to 25 January 2017 and check the status bar for the sum of these values.

You can also filter the values to check it.

Here’s How It Works.

In this formula, we have used logical operators with SUMIFS, which helps 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.

These two criteria tell the function to sum values equal to or greater than 15 January 2017 and equal to or lower than 25 January 2017.

So, at this point, we have two different criteria, one for the lower limit and one for the upper limit, which creates a range of dates.

The function will only sum those values 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 seven days. This should be dynamic, so you get the sum of the last seven days daily.

So the formulas will be:

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

This is how this formula 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 equal to or lower than today’s date.

The second criterion tells the function to sum only those values equal to or lower than the date six days before today.

Now, the range of two dates will be something like this: from today to six days ago, meaning you have seven days.

The function will only sum values in this range.

You might face a problem while using the same formula in your example; in this case, you must troubleshoot for several errors.

• Date Format: If the dates are saved as text instead of an actual date, the formula cannot sum values using that date. So check if the dates are saved in the right format.
• Correctly using > and < Operators: In this formula, you need to use the lower than and greater than operators, so make sure to use them in the right way so that it can create a correct date range to sum the values.
• Range Needs to be of Same Size: In the formula, you need to define two ranges of the dates, basically the same range of cells twice. But here, ensure the range in both references should be the same size.

Using the DATE Function to Define the Dates

In this tutorial, we have used the date from a cell to enter the formula. But if you want to enter the date directly in the formula, use the DATE function.

In the date function, you can enter the year, month, and day as a number in the argument, and the function converts it into a valid date.

=DATE(year,month,day)

Now, if you take the formula that we used above and use the DATE function, the formula will be some like below:

=SUMIFS(B2:B31,A2:A31,">="&DATE(2017,1,15),A2:A31,"<="&DATE(2017,1,25))

You can see in the formula we have used the DATE function in the first criteria, DATE(2017,1,15), which returns the date 17-Jan-2017. In the second criteria, we have used the DATE(2017,1,25), which returns 25-Jan-2017.

Back to the Excel Formulas