Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

10000+ Copies Already Downloaded

Sum Values Between Two Dates

how to sum values between two dates in excel

Let’s say, you are working in a trading company and your boss tells 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.

Here, you have a range of dates which actually includes total 15 dates and you need to sum values for all of them. In Excel, SUMIFS Function is the best way to sum values between two dates.

So today, in this post, you will learn a simple method sum values between two dates in excel using SUMIFS Function. And, you will also learn how can you create a dynamic range of dates to get the sum.

Table of Content

  1. Quick Intro To SUMIFS
  2. Sum Values Between Two Dates
  3. Use Dynamic Dates To Sum Values

Quick Intro: SUMIFS

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.

Syntax:

=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.

Learn more about SUMIFS from here.

Sum Values Between Two Dates

Here you have sales data for the month of Jan and you need to sum values between 15-Jan-2017 to 25-Jan-2017. You can download this sample date file from here to follow along.

data to sum values between two dates

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.

formula to sum values between two dates

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

check sum from status bar to verify that sum values between dates

How Does It Work?

In above formula, you 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.

how this formula works to sum values between two dates

  • First of all, you have used range B2:B31 as a sum_range where you have the amounts.
  • After that, you have referred to A2: A31 as first criteria range and “>=15-Jan-2017” as the first criterion.
  • Next, you have again referred to A2:A31, but this time as second criteria range and “<=25-Jan-2017” as a second criterion. This tells the function to sum only those value which equals to or lower than 15-Jan-2015.
  • So now, in this formula, you have two different criteria, one for the lower limit and one the for the upper limit, on the same criteria range which will create a range of dates. And, the function will only sum those values which are between this range.

Sum Values Between a Dynamic Range of Dates

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:

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

How Does It Work?

In above formula, you have used TODAY Function to get the current dynamic date which will change. 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.

And, the best part is, this range will change every single day.

Sample File

download sample excel file

Conclusion

Dates are an important factor for summarizing data. And, we often use dates as a base for our analysis. And, the formula which you have learned above will help you in your analysis.

The most important thing which you need to take care while using this formula is you need to use lower than a logical operator with upper date and greater than with the lower date.

I hope you found it useful.

And now, I want to hear from you. If you have any other method to calculate sum between tow dates, please share with me.

I would love to hear from you.

  • mma173

    I prefer Sumproduct for such scenario