Excel Sumif Between Two Dates
Sumif between two dates is when we work with data which have serial number with different dates and the condition to sum the values is based between two dates, we need to specify the conditions for the dates, the first date will be obviously smaller than the last date so we can use <= operator and >= operator to sum the values between the dates.
Explanation
With Excel, it becomes quite easy to add or subtract numeric value between two dates as a criteria/condition. We can add or subtract values between two certain dates using two functions: ‘SUMIF’ and ‘SUMIFS’.
When multiple criteria are to be specified in the ‘SUMIF’ function, then this is achieved using logical/comparison operators. Since we need to sum or subtract cell values lying in between two dates, thus both dates (mentioned as condition/criteria) have to be tested on the same range. Hence, multiple ‘SUMIF’ functions are applied to subtract or add the resulting values in one formula.
Specific date criteria would be mentioned in each ‘SUMIF’ function, and both functions would be then combined in one formula so as to get the final value via subtracting or adding the resulting values of each function.
This would look like the below syntax:
SUMIF (range, criteria1, [sum_range]) - SUMIF (range, criteria2, [sum_range])
The starting date would be criteria1, and the ending date would be criteria2.
Examples
Example #1
If we have a table consisting of two columns: one containing dates and one containing the value of transaction done. So if we wish to sum the transactions that are done after the date: 15/01/2019, and those that are done before the date: 20/03/2019, i.e sum transactions if the corresponding date is between 15/01/2019 and 20/03/2019.
Then we use the sumif function. This is done by applying the following formula:
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
=SUMIF($A$2:$A$6,”>”$E$2,”$B$2:$B$6) - SUMIF($A$2:$A$6,”<”$E$3,”$B$2:$B$6)
We can see that the first SUMIF function contains the start date as criteria with the logical expression ‘greater than’ and cell reference (that is cell E2), combined with an ‘&’ sign, and the second SUMIF function contains the end date as criteria with the logical expression ‘less than’ and cell reference ( that is cell E3), combined with an ‘&’ sign. The range argument and the sum_range argument provided in both the SUMIF are the same.
So we see that the first SUMIF will sum all transaction values where the corresponding date is greater than the start date (15/01/2019), and the second SUMIF will sum all transaction values where the corresponding date is less than the end date (20/03/2019). After this, the two resultant values are subtracted to get the final value.
Following is this illustration:
The highlighted values are added (10,000+5,000+7,000+15,000=37,000) to get 37,000 with the first SUMIF. This is so because these are the cells that satisfy the first criteria, i.e these transaction amounts are done after the start date: 15/01/2019.
This value (37,000) is then subtracted to the sum of below-highlighted cells (5,000+20,000+7,000=32,000) to get 32,000 (or cells that get added with the second SUMIF as these are the cells that satisfy the second criteria, i.e these transaction amounts are done before the end date: 20/03/2019).
So, final value= 37,000-32,000=5,000
Example #2
If we have a table consisting of two columns: one containing dates and one containing the number of assignments submitted on that date. So if we wish to sum the number of assignments that are done after the date: 15/01/2019, and those that are done before the date: 20/03/2019.
Then we use a sumif function. This is done by applying the following formula:
=SUMIF($A$2:$A$6,”>”$E$2,”$B$2:$B$6) - SUMIF($A$2:$A$6,”<”$E$3,”$B$2:$B$6)
So we see that the first SUMIF will sum all the number of assignments where the corresponding date is greater than the start date (15/01/2019), and the second SUMIF will sum all the number of assignments where the corresponding date is less than the end date (20/03/2019). After this, the two resultant values are subtracted to get the final value.
Following is this illustration:
The highlighted values are added (12+5+7+15=39) to get 39 with the first SUMIF. This is so because these are the cells that satisfy the first criteria, i.e these number of assignments are submitted after the start date: 15/01/2019.
This value (39) is then subtracted to the sum of below-highlighted cells (5+20+7=32) to get 32 (or cells that get added with the second SUMIF as these are the cells that satisfy the second criteria, i.e these number of assignments are submitted before the end date: 20/03/2019).
So, final value= 39-32=7.
Recommended Articles
This has been a guide to Sumif Between Two Dates and other criteria in excel. Here we discuss the difference between two dates using SUMIF Function in Excel with some examples and downloadable excel template. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
