Updated May 9, 2023
Sumif Between Two Dates
Sumif between the two dates is a useful and easy function. By this, we can calculate the total of any specified range between dates. This allows us to get a specific sum of any interval data. For calculating the sum between two specific dates, we will use the Sumifs function.
To calculate the total of any specified criteria and range in Excel, you can use the Sumifs function. 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 and 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 for some products, as shown below.
As we can see, the Date of Order column has different dates on which the product was sold. Now with the help of Sumifs, we need to calculate the sum of the product sold between two dates. For this, we must select two dates between the dates mentioned in the Date of Order columns in separate cells.
As shown in the screenshot below, we have taken 3/19/2019 and 3/5/2019, which are in the middle portion.
Before we learn to calculate the sum between two dates, let’s understand the complete syntax of the Sumifs function, which we will use.
The syntax of Sumifs is explained below;
- Sum_Range = This is the sum range or date that must be added.
- Criteria_Range1 = This is the first criteria range we need to get the sum. There may be more than 1 criterion.
- Criteria 1 = This is the first criterion for seeing the sum output. For this as well, there can be more than 1 criterion.
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, type the “=” (Equal) sign, search, and select the SUMIFS function. Now select the defined criteria and range as explained above.
Select the Sum Range as D2 to D20 and Criteria Range 1 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 criteria 1 and criteria 2 is used for concatenating “<=” and “>=” signs with dates.
Once done, press enters to see the result. As shown below, the Sumifs function has calculated the sum between 3/19/2019 and 3/5/2019 as 392.
To test the obtained result, let’s change the dates between the selected dates range. As we see below, we can still get the result between the selected after and before dates even when we change the dates.
Sumif Between Two Dates in Excel – Example #2
There is a way to use the same syntax a little differently. Here is the syntax used previously.
=SUMIFS(D2:D20,B2:B20,”<=”&G2,B2:B20,”>=”&G3)
Now let us change the way we wrote and framed the syntax. In the above syntax, in place of criteria 1 and 2, we have selected the cells with the dates. 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 the 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 the quantity sold between 3/14/2019 and 2/28/2019 as 611.
Considering the After date first and Before date last or using the correct criteria if we change the date is significant. For testing, let’s interchange the date sequence by taking the Before date first and the After date last, as shown below.
As we can see, we got “0”. This means the data cannot be summed as the selected range gives criteria 1 and criteria 2 both have 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 After the date and then select Before the date in the second half.
- A cell containing dates or manually entered dates give the same result.
- We can use a single date, which 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, examples, and a downloadable Excel template. You can also go through our other suggested articles –
