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