SUMIF Between Two Dates

What Is Excel SUMIF Between Two Dates?

The Excel SUMIF between two dates is a formula that helps us add values specified in the source dataset between two given dates. And since a date interval is involved in the calculations, we use the logical operators “<”, “>”, “<=”,and “>=”, in the SUMIF function in the criteria argument.

Users can use the SUMIF in a date range formula in the finance domain to determine the total sales between two dates.

For example, the following image shows two datasets. While the first one shows the sales generated at a firm’s branch offices on a set of dates, the second contains a date range’s start and end date.

SUMIF between Two dates - Intro

The task is to determine the total sales generated in the date range specified in the second dataset and display the output in cell B12. Also, we shall include the start and end dates in the calculation.

Then, we can apply the SUMIF between two dates formula in the target cell to achieve the required data.

SUMIF between Two dates - Intro - Output

We use the SUMIFS function-based SUMIF between two dates formula in the above example.

The Excel SUMIFS function checks two criteria. The first criterion is determining the cells in the cell range B2:B6, containing dates greater than or equal to the start date cited in cell B9. On the other hand, the second criterion is to find the cells in the cell range B2:B6 containing dates less than or equal to the end date specified in cell B10.

Further, there are cells in the range B2:B6, where the first criterion holds. So, the function proceeds to check the second criterion. Thus, the function checks for cells in the range B2:B6, where both the criteria hold.

Since the two criteria hold in cells B3:B6, the function adds the corresponding sales figures in cells C3:C6 to return $32,000 as the total sales generated in the specified date range.

Key Takeaways
  • The formula to determine the SUMIF between two dates enables us to sum values based on the two specified dates.
  • Users can use the sum if between two dates formula to add values, such as sales figures and statistical data based on the specified start and end dates.
  • We can use the built-in function SUMIFS to determine the SUMIF between the two specified dates.
  • We can use Excel VBA coding to apply the SUMIFS function to determine and display the SUMIF between the two given dates in the required target cell.

Syntax

We can determine the sum of the required values in a date range using SUMIF between two dates syntax shown below:

Including Start And End Dates

=SUMIFS(sum_range,date_range,”>=start_date”,date_range,”<=end_date”)

Excluding Start And End Dates

=SUMIFS(sum_range,date_range,”>start_date”,date_range,”<end_date”)

We must ensure that we use the appropriate formula to fit our requirements. Also, the start date must be smaller than the end date. Otherwise, we may face the scenario of SUMIF between two dates not working.

SUMIF Between Two Dates Explained

The steps to SUMIF between the given two dates are as follows:

  1. Ensure the source dataset contains valid dates and that the values to add are correct. Also, the start and end dates of the required date range must be valid.

  2. Choose a target cell to sum values in the specified date range.

  3. Type =SUMIF( in the target cell. [ Alternatively, type =SU or =SUM and double-click SUMIFS in the Excel suggestions to choose the function.]

  4. Enter the range from the source dataset, containing the values to add based on the dates criteria, as the first argument value. Next, enter a comma.
    After that, enter the date range from the source dataset containing the dates to check and determine those greater or equal to the specified start date, followed by a comma.

    Next, enter the logical operator “>=” or “>” within double quotes based on whether we must include or exclude the start date in the calculation.
    After that, we must enter the start date.
    If we must enter a date value directly in the formula, ensure to enter the logical operator and the date value in the valid Excel date format within double quotes.

    “>=start_date” or “>start_date”

    On the other hand, if we must enter the cell reference to the required date value. Then, enter the logical operator in double quotes, the “&” symbol, and the cell reference to the date value.

    “>=”&cell_reference or “>”&cell_reference

    Further, the best practice is to enter a date value using the Excel DATE function. So, we can enter the logical operator in double quotes, “&”, and the DATE() with the year, month, and day arguments supplied to make it return the required start date.

    “>=”&DATE(year,month,day) or “>”& DATE(year,month,day)

    Next, enter a comma.
    After that, enter the date range from the source dataset containing the dates to check and find those less or equal to the specified end date, followed by a comma.
    Next, enter the logical operator “<=” or “<” within double quotes based on whether we must include or exclude the end date in the calculation.
    After that, we must enter the end date, with ways to enter the end date being the same as explained for the start date.

  5. Close the bracket and press Enter to view the sum of values in a date range using SUMIF between two dates function.

Here is how the formula works.

First, the SUMIFS() checks the date range in the source dataset to determine the dates greater than or equal to the specified start date. Next, if there are cells in the date range where the criterion holds, the function proceeds to the next criterion check.

Next, the function checks the date range in the source dataset to find dates less than or equal to the specified end date.

Thus, the SUMIFS() checks multiple criteria and determines the cells in the source date range where both criteria hold. Finally, the function adds the corresponding values in the same rows as the cells where both the criteria hold in the range specified as the SUMIFS()’s first argument, sum_range.

Thus, in this way, we obtain the required sum of values between the specified start and end dates.

However, if there are no cells in the source date range where the first criterion holds, the function does not check the second criterion and returns 0 as the output.

Furthermore, we must ensure the dates and numbers to add are in the valid date and number formats. They must not be text strings. Also, the criteria syntax must be correct, as explained above.

Further, ensure all the ranges specified in the formula are the same size. Otherwise, the function’s return value will be the #VALUE! Excel error.

Thus, adhering to the above precautions will help us avoid the scenario of SUMIF between two dates not working.

Examples

Check out the following SUMIF between two dates examples to use the formula effectively.

Example #1

The following image shows two datasets.

While the first one contains a list of stationery items, their units sold and date of sales data, the second contains the start and end dates of a date range.

SUMIF between Two dates -Example 1

The requirement is to determine the total units of the listed stationery items sold during the specified date range, excluding today’s date, and show the output in cell F5.

Then, the steps are as follows:

Step 1: Choose the target cell F5 and enter the following SUMIFS().

=SUMIFS(B2:B9,C2:C9,”>=”&TODAY()-7,C2:C9,”<“&TODAY())

SUMIF between Two dates - Example 1 - Step 1

Step 2: Press Enter to view the total units sold in the past week, excluding today’s date.

SUMIF between Two dates - Example 1 - Step 2

First, the Excel TODAY function returns today’s date. So, the first criterion in the SUMIFS() checks the date range C2:C9 for cells containing dates greater than or equal to the date seven days before today’s date. In this case, the qualifying cells are C6:C9.

Next, the SUMIFS() checks the second criterion. It checks the date range C2:C9 for cells containing dates less than today’s date. In this case, the qualifying cells are C2:C9.

Thus, the two criteria hold in cells C6:C9, leading to the function adding the cells B6:B9 values to return 1095 as the total units sold in the past week, excluding today’s date.

Example #2

We shall see an example of SUMIF between two dates and another criteria.

The image below shows two datasets.

While the first dataset lists teams, their new joiners count, and their joining dates, the second contains a date range’s start and end dates. Also, the second dataset cites the team name containing the phrase “HAL”.

SUMIF between Two dates - Example 2

The task is to determine the number of joiners who joined the teams, with names containing the phrase “HAL”, between the dates specified in the second dataset. Assume the target cell is cell B15.

Then, we can use the SUMIF between two dates and another criteria formula in the target cell to obtain the required output.

Step 1: Choose the cell B15 and enter the following SUMIFS().

=SUMIFS(B2:B8,C2:C8,”>”&C12,C2:C8,”<“&C13,A2:A8,”HAL*”)

SUMIF between Two dates - Example 2 - Step 1

Step 2: Press Enter to view the formula output.

Example 2 - Step 2

First, the SUMIFS() checks the date range C2:C8 for cells containing dates greater than the start date and less than the end date. In this case, the qualifying cells are C4:C8.

Next, the function checks the range A2:A8 for team names containing the phrase “HAL”, which are A2, A4:A5, and A8. We use the wildcard character, “*”, with the required phrase “HAL“, representing all characters following the specified phrase. Thus, the wildcard character helps find the team names containing “HAL”, present along with other characters.

Thus, the three criteria hold in rows 4, 5, and 8. It leads to the SUMIFS() adding the cells B4, B5, and B8 values to return 54 as the total new joiners based on the specified conditions.

Important Things To Note

  • The dates and numbers in the formula to determine the SUMIF between two dates must be in the valid date and number formats to avoid potential errors.
  • Use the correct Excel logical operators in the formula to find the sum between two dates based on whether to include or exclude the start and end dates.
  • The sum_range and date_range sizes must be equal in the SUMIFS() to ensure the function works correctly.

Frequently Asked Questions (FAQs)

1. How to use SUMIF between two dates VBA?

We can use SUMIF between two dates VBA using the following steps, explained with an example.
The following image shows two datasets. While the first one lists dates and the number of assignments submitted on each date, the second shows a date range’s start and end dates.

FAQ 1

The requirement is to calculate the total number of assignments submitted in the date range specified in the second dataset and display the output in cell B14.

Then, here is how to use Excel VBA coding to obtain the required data.

Step 1: Open the worksheet containing the source dataset and press Alt + F11 to access the VBA Editor.

FAQ 1 - Step 1

Step 2: Choose the required VBAProject. Next, select Module in the Insert tab in the menu to open a new module window.

FAQ 1 - Step 2

FAQ 1 - Step 2 - Module.jpg

Step 3: Enter the VBA code in the module to determine the SUMIF between the two given threshold dates.

FAQ 1 - Step 3 - code

Step 4: Press the play icon in the menu to run the code.

FAQ 1 - Step 4

Finally, open the active worksheet to view the required total number of assignments submitted in the specified date range in cell B14.

FAQ 1 - Step 4 - result

The code defines the SUMIFS() in the sub-procedure Sumif_Between_Two_Dates().

The function checks for cells in the range A2:A7, containing dates greater than or equalling the start date and less than or equalling the end date. In this case, the qualifying cells are A3:A5.

Finally, the function adds the values in the corresponding cells in the same rows of column B, B3:B5. And hence, it returns 47 as the total number of assignments submitted in the specified date range.

2. Are there any common errors to watch out for when using SUMIF between two dates?

There are common errors to watch out for when using SUMIF between two dates, which are as follows:

•Incorrect date and number formats can make the formula return 0, even if the applied formula is correct.
• An incorrect criteria syntax will lead to Excel not executing the formula. Instead, a message box opens, stating that the formula has a syntax issue.
• Using incorrect logical operators before the start and end dates can lead to typo errors.
• Different sizes of the sum_range and date_range will result in the SUMIFS() returning the #VALUE! error value.

3. What format should I use for the date criteria in SUMIF between two dates?

You can use the following format options for the date criteria in SUMIF between two dates:

• Enter the date value in the formula immediately after the logical operator, with the entire term in double quotes.
• Enter the logical operator in double quotes, followed by the “&” symbol and the cell reference the required date value.
• Enter the logical operator in double quotes, followed by the “&” symbol and the DATE(year,month,day) or the TODAY().

Download Template

This article must be helpful to understand the SUMIF Between Two Dates, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to What Is SUMIF Between Two Dates. Here we learn how to use the Excel SUMIFS function to add values between two dates, with examples. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *