SUMIF Between Two Dates

Excel Sumif Between Two Dates

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

General Syntax for ‘SUMIF’ function is as follows:

The SUMIF function syntax has the following arguments:

• range: Required, represents the range of cells we wish to apply the criteria against
• criteria: Required, represents the condition or criteria to be met. This could be supplied in the form of a number, date, text, cell reference, logical expression, or any other Excel function
• sum_range: Optional represents the cells to sum if the criteria are met. This argument needs to be mentioned only when we wish to sum cells other than defined in the range argument. If omitted, the function will sum the same cells to which the condition/criteria is applied.

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 ending date would be criteria2.

Examples of Sumif Between Two Dates and other criteria in Excel

Following are the examples of Sumif Between Two Dates:

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:

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

Things to Remember About SUMIF Between Two Dates in Excel

• The ‘SUMIF’ function in Excel is a built-in function that is categorized as a Mathematical/Trigonometry function.
• The ‘SUMIF’ function usually returns a numeric value.
• The sum_range and range arguments provided as an argument to the function should be ranged, and not arrays.
• Any criteria consisting of mathematical symbols or text criteria mentioned as an argument in the function must be enclosed in double quotation marks.
• The sum-range argument criteria mentioned as an argument in the function need not necessarily be of the same size as the range argument, however in the case where we are using SUMIF between two dates, then the sub-range argument criteria mentioned as an argument in the function has to be of the same size as the range argument.
• The ‘SUMIF’ function in Excel can also be used as a worksheet function. As a worksheet function, it can be entered as part of a formula in a cell of a worksheet.
• If sum_range is omitted, then the actual cells that are added are those that are provided as a range argument to the function.
• SUMIFS function allows to impose or use more than one criteria without the use of logical/comparison operators.
• SUMIF function can reference other workbooks or worksheets only if they are currently open.

Recommended Articles

This has been a guide to Sumif Between Two Dates and another 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 –

• How to use VLOOKUP Tutorial in Excel? (with Examples)
• SUMIF on VLOOKUP
• Using VLOOKUP with SUM
• SUMPRODUCT Excel Formula
• DSUM in Excel
• Using SUMPRODUCT with Multiple Criteria