Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to Sum Values Between Two Dates in Excel

In Excel, you can sum numeric value between two dates as conditions or criteria. In this case, you need to apply two dates as criteria, so you are going to deal with multiple criteria to sum these values. In this article, we are going to sum such values using two methods.

Sum Values Between Two Dates in Excel 

You can add the values between two dates using either the SUMIF or SUMIFS functions. Here are explanations of both methods.

Using the SUMIF function

Generally, as an Excel user, you might be wondering how to use SUMIF function to sum values between two dates. As we are aware that SUMIF handles only one criterion as per syntax, so it seems impossible to supply two dates as criteria in the criteria argument to sum values in the sum_range argument.

Syntax;

SUMIF(range, criteria, [sum_range])

When you think about using multiple criteria in the SUMIF function, then you are going to deal with OR logic. OR logic says SUMIF function sums the values if any of the conditions or criteria are met.

In this article, you will learn how to use SUMIF using dates as criteria. As you need to sum value between two dates, so both dates criteria need to be tested on same range argument.

As per OR logic, you need to apply multiple SUMIF functions to add or subtract resulting values in a single formula. As you need to sum numbers between two dates so each date criteria will be supplied in each SUMIF function, and both SUMIF functions will be combined in a single formula to get the final value by adding or subtracting the resulting values of each SUMIF function.

To get the final value of numbers between two dates, the following will be the syntax of formula;

=SUMIF(range, criteria1, [sum_range]) - SUMIF(range, criteria2, [sum_range])

Here, criteria1 is starting date and criteria2 is ending date. Both criteria should be applied on same range argument in both the SUMIF function and sum_range argument should also be the same in both SUMIF functions as given in the above formula.  

In this example, suppose you have a data set of various orders delivered on different dates. This dataset contains Order_ID, Date and Amount fields. You need to find the sum of those orders’ amounts which fall between two specified dates. These two dates have been specified as Start Date (Criteria1) and End Date (Criteria2) as given below. You need to use two SUMIF functions in a single formula to sum order amounts between given two dates. By following the above-given formula syntax, you need to apply the following formula to get the final result.

=SUMIF($B$2:$B$11,">="&$F$2,$C$2:$C$11)-SUMIF($B$2:$B$11, ">"&$F$3,$C$2:$C$11)

In this formula, we have applied two SUMIF functions containing two dates as criteria. First SUMIF function contains Start Date (Criteria1) with logical expression Greater Than Equal To and cell reference of the start date, combined with an ampersand & (“>=”&$F$2). Second, the SUMIF function in the formula contains an End Date (Criteria2) with a logical expression Greater Than and cell reference of an end date, combined with an ampersand & (“>”&$F$3). You can see the range argument and sum_range argument in both SUMIF functions are same.

 

How This Formula Works

In the above formula, first SUMIF function will sum all the orders’ amounts where the date is Greater Than Equal to Start Date (7/20/2018). And second SUMIF function will sum all the orders’ amounts where the date is Greater Than End Date (8/10/2018). Finally when formula subtracts the resulting figures of these two SUMIF functions, then it gives us the sum of values that fall between two dates.

Using SUMIFS function

The SUMIFS function is a very handy function to sum values based on multiple criteria. By default, the SUMIFS function is designed to deal with multiple conditions or criteria to sum values. This function works based on the AND logic. AND logic in SUMIF between two dates means to sum values where both dates’ conditions are TRUE.

The syntax of SUMIFS function is;

SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,....)

As per its syntax, you need to supply a range of cells where values need to be the sum and each range of cells where each criterion is tested to sum these values. In our example, as given above, dates are supplied in the same range, so we will test both criteria on the same criteria_range argument in SUMIFS function as given below.

=SUMIFS($C$2:$C$11,$B$2:$B$11,">="&$F$2,$B$2:$B$11,"<="&$F$3)

Now, this method is very easy to understand and apply to sumif values between two dates. This formula says, sum the values in sum_range C2:C11 where the date is Greater Than Equal to “Start Date” (F2) and Less Than Equal to “End Date” (F3) in criteria_range B2:B11.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

See Also:

How to Use SUMIF Function in Google Sheets – Excelchat

How to Use SUMIF with Multiple Criteria – Excelchat

How to Create a Calculated Field in a Pivot Table – Excelchat

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I need a formula where s36 = 1 if a10=s7 and c10=any value?
Solved by G. J. in 13 mins
Hello good afternoon, I have a problem with excel. I need a formula to compare data between 3 columns and give me the result in a fourth column
Solved by X. B. in 58 mins
I have a list or people who are ranked in numbers from 3 to 6 I need to recognize the contents of each multiple cells and create an equivalent letter. for that value into another column. e.g. Cell E1 = 3 to show in new cell that row (H1) the letter "C" I have multiple rows with different values in column "E" Can this be done?
Solved by I. J. in 30 mins
Hi, I am trying to find an average of one column 'C', where another column = a particular value, or a third column = another particular value.
Solved by T. C. in 19 mins
amending a formula so that there is another criteria and if it does not meet this criteria then we stick with the original criteria
Solved by A. B. in 40 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc