SUMIF Between Two Dates

SUMIF between two dates is when we work with data with a serial number with different dates, and the condition to sum the values is based on two dates. So, we need to specify the conditions for the dates. The first date will be smaller than the last date, so we can use the <= operator and >= operator to sum the values between the dates.

For example, consider the below table showing the sales amount and dates of various products. We also have the start and end date.

Now, let us find how to find SUMIF between two dates in Excel.

The steps are:

Step 1: Since we have the start and end dates, let us enter the SUMIF formula.

The formula is =SUMIF($A$2:$A$6,">"&$F$1,$B$2:$B$6)-SUMIF($A$2:$A$6,">"&$F$2,$B$2:$B$6)

Step 2: Press Enter key.

We can see the result as shown in the image below.

Likewise, we can find SUMIF between two dates in Excel.

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, ) - SUMIF (range, criteria2, )

The starting date would be criteria 1, and the ending date would be criteria 2.

SUMIF between two dates is a scenario when the data has different dates as serial numbers and the condition to sum of two values is based on two dates. It is important to note that the conditions for the dates must be specified to derive desired results out of the function.

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

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.

Now that we have a basic understanding of the concept and its uses, let us understand the practicality of the concept and its related factors through the examples below.

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:

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

Following is this illustration:

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.

• SUMIF between two dates in Excel function is used to find the sum between two dates in the worksheet.
• We need to have two dates – start date and end date.
• The start date is usually smaller than the end date.