SUMIFS is the easiest method to sum values between two dates (range of dates).

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

In this case, you need to sum 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 total 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 all the values between that range.

*Confused ? Don’t worry.*

Today in this post, I’d like to share with this simple formula and explain it in plain language to make you understand it.

…let's get started.

It can help you to sum values by specifying more than one criteria. In short, it will only sum those cells where all the conditions are met.

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

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

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

Download this sample file from here to follow along.

In the D2 cell, insert below formula 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 date as well.

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

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** of all, we have used range B2:B31 as a sum_range where we have the amount.

**After that**, we have referred to A2:A31 as first criteria range and “>=15-Jan-2017” as the first criterion.

**Next**, we have again referred to A2:A31, but this time as second criteria range and “<=25-Jan-2017” as a second criterion.

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

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

And the function will only sum those values which are within this range.

There is also a situation when you need to sum values between two dates on an ongoing basis.

Let’s say, you need to sum values for last 7 days and this should be dynamic so that every day you get the sum of last 7 days.

So, the formulas will be:

In the above formula, we have used 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 equals to or lower than today’s date.

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

**Now**, the range of two dates will be something like this: from today to six days back which means you have total seven days.

And the function will only sum values which are in this range.

Download this sample file from here to learn more.

As I said, SUMIFS is the best way to get the sum of values between a range of two dates.

But one thing which you need to take care is to use lower than sign with upper date and greater than with the lower date.

And, I hope you have found this formula tip useful but you need to tell me one thing.

*Do you know any other method to sum values between two dates?*

Please share your views with me in the comment section, I'd love to hear from you. And, don't forget to share this tip with your friends.

**Related Excel Tutorials**

- SUMIF OR: By using OR logic you can sum values based on two different criteria which is not normally possible with SUMIF [...]
- SUMIF with Wildcard Characters: By using wildcard characters with SUMIF you can use a partial match criteria [...]
- SUMPRODUCT IF: It make it possible to product values from two or more arrays using a specific condition [...]
- Get Total Days in Month: You can calculate total number of days in month using a formula [...]
- Get Month from a Date: A month is one of the useful components of a date which you can use to summarize data and when it comes to Excel we have different methods [...]

- mma173