SUMIF Between Two Dates

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Excel 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.

SUMIF between Two dates Intro

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.

SUMIF between Two dates Intro - Output

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

Key Takeaways

  • SUMIF between two dates in Excel function is used to find the sum between two dates in the worksheet.
  • This premade function of Excel helps in calculating the sum of data points within a range on true or false conditions.
  • These conditions are referred to as criteria and can gauge if the given criteria are met or otherwise.
  • It can check if a number is greater (>) than the other, lesser (<) than, or equal to (=) the other data point.

Syntax

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 criteria 1, and the ending date would be criteria 2.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

SUMIF Between Two Dates Explained

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

Examples

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.

You can download this Sumif Between Two Dates Excel Template here – Sumif Between Two Dates Excel Template

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.

Sumif Between dates Example 1

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.

Sumif Between dates Example 1-1

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:

Example 1-2

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

Sumif Between dates Example 1-3

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

Sumif Between dates Example 1-3

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.

Sumif Between dates Example 2

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.

Example 2-1

Following is this illustration:

Example 2-2

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

Example 2-3

So, final value= 39-32=7.

Sumif Between dates Example 2-4

Important Things To Note

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

Frequently Asked Questions

1. What is SUMIF between two dates mean?

SUMIF between two dates helps users find the data between two dates. Since the data is calculated between two dates, i.e., the start date and end date, we have to specify the dates while using this function.

2. Explain how to find SUMIF between two dates with an example.

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

SUMIF between Two dates - FAQ 2

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.

SUMIF between Two dates - FAQ 2 - Step 2

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

3. What is the syntax of SUMIF between two dates in Excel?

The syntax of SUMIF between two dates is =SUMIF(range,criteria1,[sum_range])-SUMIF(range,criteria2,[sum_range]) where starting date is criteria 1 and end date is criteria 2 respectively.

Recommended Articles

This article is a guide to Sumif Between Two Dates and. Here we explain the difference between two dates using SUMIF Function in Excel with examples. You may learn more about Excel from the following articles: –