Excel Sumif Between Two Dates (Table of Contents)
Sumif Between Two Dates
Sumif between the two dates is quite useful and easy function. By this, we can calculate the total of any specified range between any dates. This allows us to get a specific sum of any interval data. For calculating the sum between two specific dates we will use Sumifs function.
Sumifs in excel is used for calculating the total of any specified criteria and range. The major difference between Sumif and Sumifs is that in Sumifs we have to use and add more than one criteria”s in a single function argument instead of adding a separate syntax in the same cell.
Sumifs function can be accessed from Insert Function from the Math & Trig category.
How to Use Sumif Between Two Dates in Excel?
Sumif between two dates in excel is very simple easy to use. Let us now see how to use the Sumif between two dates in Excel with the help of some examples.
Sumif Between Two Dates in Excel – Example #1
We have owner wise sales data of some products as shown below.
As we can see, Date of Order column has different dates in which the product was sold. Now with the help of Sumifs, we need to calculate the sum of the product sold in between two dates. For this, we need to select two dates between the dates mentioned in Date of Order columns in separate cells.
4.9 (4,466 ratings)
View Course
As we can see in the below screenshot, we have taken two dates 3/19/2019 and 3/5/2019 which are situated at the middle portion.
Before we learn to calculate the sum between two dates, let’s understand the complete syntax of Sumifs function which we are going to use.
Syntax of Sumifs is explained below;
- Sum_Range = This is the sum range or date which needs to be added.
- Criteria_Range1 = This is the first criteria range for which we need to get sum. There may be more than 1 criteria.
- Criteria1 = This is the first criteria under which we will see the sum output. For this as well, there can be more than 1 criteria.
Now let’s apply Sumifs and calculate the sum between the two dates. For this go to the cell where we need to see the output and type “=” (Equal) sign and search and select SUMIFS function. Now select the defined criteria and range as explained above.
Select the Sum Range as D2 to D20, Criteria Range1 as B2 to B20. Now for criteria1 add “<=”&G2 (After Date 3/19/2019). In the next phase of the same syntax, select the criteria Range2 again as B2 to B20 and for criteria2 add “>=”&G3 (Before Date 3/5/2019) as shown below.
Note: Ampersand (&) in criteria1 and criteria2 is used for concatenating “<=” and “>=” signs with dates.
Once done press enter to see the result. As we can see below, Sumifs function has calculated the sum between 3/19/2019 and 3/5/2019 as 392.
For testing of the obtained result, let change the dates between the selected dates range. As we can see below, even when we are changing the dates, we are still able to get the result between the selected after and before dates.
Sumif Between Two Dates in Excel – Example #2
There is a way to use the same syntax little differently. Here is the syntax used previously.
=SUMIFS(D2:D20,B2:B20,”<=”&G2,B2:B20,”>=”&G3)
In the above syntax, in place of criteria1 and criteria2, we have selected the cells which have the dates. Now let us change the way we wrote and framed the syntax. And for this, we will type the dates between which we need to calculate the sum using Sumifs as shown below.
As we can see in the above screenshot, we have entered the After date as of 3/14/2019 and Before date as of 2/28/2019. Between these dates, we will calculate the sum of the quantity sold out. To get the result, press enter.
Once we do that, we will get the sum of quantity sold between 3/14/2019 and 2/28/2019 as 611.
There is a significance of considering After date first and Before date in last or using the correct criteria if we are changing the date. For testing let’s interchange the date sequence by taking Before date first and After date last as shown below.
As we can see, we got “0”. Which means the data cannot be summed as the selected range gives criteria1 and criteria2 both have the dates. Hence, we got “0” as a result.
Pros of Sumif Between Two Dates
- This straight-through gives the result between selected dates.
- Syntax of the Sumifs looks complex but implementation is easy.
- After and Before dates can also be replaced with Today’s date if the data is of that kind.
Cons of Sumif Between Two Dates
- Sometimes people may unknowingly replace or interchange the before and after dates, so in this case, the obtained result will be “0”.
Things to Remember
- Always select the Before and After dates in sequence. In the first half of the syntax select the After date and then in the second half of the syntax select Before date.
- A cell containing dates or manually entered dates give the same result.
- We can use a single date and that will give the result respected to that specific date only. Which will not be the sum of any data set.
- Use ampersand to concatenate the dates with criteria as dates are chosen by selecting the specific cells.
You can download this Sumif Between Two Dates Excel Template here – Sumif Between Two Dates Excel Template
Recommended Articles
This has been a guide to Excel Sumif Between Two Dates. Here we discuss How to use Sumif Between Two Dates in Excel along with examples and downloadable excel template. You can also go through our other suggested articles –
