Excel Sumif Between Two Dates

Explanation

With Excel, adding or subtracting numeric values between two dates as a criteria/condition becomes quite easy. For example, we can add or subtract values between two certain dates using two functions: SUMIF and ‘SUMIFSSUMIFSSUMIFS is an enhanced version of the SUMIF formula in Excel that enables you to sum any range of data by matching several criteria. read more’.

When multiple criteria are specified in the SUMIF function, this is achieved using logical/comparison operators. For example, since we need to sum or subtract cell values lying between two dates, thus we must test both dates (mentioned as condition/criteria) on the same range. Hence, multiple SUMIF functions are applied to subtract or add the resulting values in one formula.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: SUMIF Between Two Dates (wallstreetmojo.com)

We must mention specific date criteria in each SUMIF function. We can then combine both functions in one formula 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

Suppose we have a table consisting of two columns: dates and the value of the transaction performed, so, if we wish to sum the transactions done after 15/01/2019 and those done before 20/03/2019, i.e., sum transactions if the corresponding date is between 15/01/2019 and 20/03/2019.

Then, we can use the SUMIF function. It 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 the logical expression “greater than” and cell reference (cell E2), combined with an ‘&’ sign. The second SUMIF function contains the end date as criteria with the logical expression “less than” and cell reference (cell E3), combined with an “&” sign. The range argument and the sum_range argument provided in the SUMIF are the same.

So, we can see that the first SUMIF will sum all transaction values where the corresponding date exceeds 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. It is so because these cells 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 from 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, which is these transaction amounts made before the end date: 20/03/2019).

So, final value= 37,000-32,000=5,000

Example #2

Suppose we have a table consisting of two columns: dates and the number of assignments submitted on that date, so if we wish to sum the number of assignments done after the date: 15/01/2019 and those done before the date: 20/03/2019.

Then, we can use a SUMIF function. We can do it by applying the following formula:

So, we see that the first SUMIF will sum up the number of assignments where the corresponding date exceeds the start date (15/01/2019). The second SUMIF will sum up 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.

The highlighted values are added (12+5+7+15=39) to get 39 with the first SUMIF. Because these are the cells that satisfy the first criteria, these 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. These numbers of assignments are submitted before the end date: 20/03/2019).

So, final value= 39-32=7.

This article guides the SUMIF Between Two Dates and other criteria in Excel. Here, we discuss the difference between two dates using the SUMIF function in Excel with some examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Excel Text SumIFSUM Shortcuts in ExcelSUMIF on VLOOKUPSUMPRODUCT Excel Formula