SUMIFS With Dates

What Is Excel SUMIFS With Dates?

The Excel SUMIFS with dates formula adds values in the given dataset based on the cited dates criteria. Typical, we utilize the logical operators, “=”, “<“, “>“, “<=“, and “>=“, in the SUMIFS() in the date-based criteria arguments.

Users can utilize the SUMIFS formula involving dates-based conditions to compute values such as the total revenue between a given date range.

For example, the source dataset holds the date-wise revenue generated at five branch offices of a firm. Also, we have a specific date range in the second dataset.

The aim is to find the total revenue in the given date range and showcase the result in cell B12, with the date range limits considered in the evaluation. Also, the target cell data format is updated as Currency in the Number Format option in the Home tab.

Then, secure the required sum value using SUMIFS with dates expression in the target cell.

In this specific SUMIFS with dates as criteria example, the SUMIFS function verifies two conditions.

The first condition checks for cells in cell B2:B6, holding dates that fall after or are the specified beginning date. Next, the second condition is to identify cells in cells B2:B6, holding dates before or the mentioned last date.

So, firstly, the function finds cells that meet the first condition, which are cells B5 and B6. Next, the function verifies the second condition. Hence, the function looks for cells among B5 and B6, where the second criterion holds.

The two conditions are true in cells B5:B6. So, the function sums the revenue figures in the corresponding rows of column C, i.e., the cells C5:C6 values to return $18,100 as the required total revenue in the cited date range.

What Is Excel SUMIFS With Dates?

The expression to evaluate the SUMIFS with dates enables us to add numbers based on the specified dates-based criteria.
Users can use the dates criteria-based SUMIFS function to sum financial and statistical data based on the given date range. We can also add additional conditions along with the dates-based criteria for yielding practical outcomes.
While we can enter the dates criteria-based SUMIFS function directly in the required cell, we can use the SumIfs Excel VBA method to apply the function in the target cell.

Syntax

The SUMIFS with dates as criteria formula syntax is the following:

Start And End Dates Considered

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

Start And End Dates Not Considered

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

Typically, the formula will be SUMIFS with dates less than or greater than the specified date range. So, depending on whether we must consider the date range limits in the evaluation, we can include the “=” sign along with the other Excel logical operators.

Furthermore, the syntax remains the same for SUMIFS with dates Google Sheets.

How To Use SUMIFS With Dates?

The steps to apply the SUMIFS function with dates-based criteria are as follows:

Choose a target cell to display the output.
Type =SUMIFS( in the target cell. [ Alternatively, type =SU or =SUM and click SUMIFS in the suggestions to select the function.]
Enter the argument values, separated by commas.

If we supply a date value directly in the function, it should be the logical operator followed by the date value in the valid date format within double quotations.

On the other hand, if we supply the cell reference to the specific date value. Then, we must enter the applicable logical operator in double quotations, the “&” symbol, followed by the cell reference to the cited date value.

Close the bracket and press Enter to secure the required outcome in the target cell.

Also, the above steps apply to SUMIFS with dates Google Sheets as well.

Examples

The following illustrations explain how to use the SUMIFS function with the dates criteria effectively.

Example #1

The source dataset contains the date-wise tasks completed data of specific teams at a firm.

The aim is to compute the total tasks completed by a specific category of teams, NTT, in the date range specified in cells C11:C12 while excluding the date range limits. We shall take cell B14 as the target cell.

Then, use the SUMIFS with dates less than and greater than formula in the target cell to acquire the required output.

Step 1: Choose cell B14, enter the following Excel SUMIFS function, and press Enter.

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

First, the SUMIFS() inspects for cells in cells A2:A8 that contain the team names starting with the phrase NTT, which are cells A2, A4, A5, and A8. The Excel Wildcard character, “*”, helps the function to locate such cells successfully.

Next, the function verifies the second condition, which is checking for cells in rows 2, 4, 5, and 8 of column B containing dates after the cited beginning date. Cells B4, B5, and B8 meet the second condition. After that, the function examines the third condition, which is checking for cells in rows 4, 5, and 8 of column B containing dates less than the cited last date. Cells B4, B5, and B8 meet the third condition as well.

So, the function adds the tasks completed values in the corresponding rows 4, 5, and 8 of column C, which are the cells C4, C5, and C8 values. Therefore, it returns 77 as the required sum value.

Example #2

The source dataset contains the date-wise inventory level data at a store.

The requirement is to determine the total inventory level in the date range cited in cells E2:E3. While the beginning date falls a week before today’s date, the ending date is the current date. We shall consider cell E5 as the target cell.

Then, acquire the required output using SUMIFS with dates formula, which includes the Excel TODAY function.

Step 1: Select cell E5, enter the following SUMIFS(), and press Enter.

=SUMIFS(B2:B10,A2:A10,”>=”&TODAY()-7,A2:A10,”<“&TODAY())

Firstly, the TODAY() returns the serial number equivalent to the current date, 3/14/2024. So, the term TODAY()-7 will return the serial number equivalent to the date seven days before the current date, which is 3/7/2024.

Next, the SUMIFS() looks for cells in cells A2:A10 containing dates exceeding or equal to the cited beginning date, 3/7/2024. Cells A6 and A8 meet the first condition. After that, the function examines the second condition, which is checking for cells in rows 6 and 8 of column A containing dates less than the cited ending date, 3/14/2024. Cells A6 and A8 satisfy the second condition as well.

So, the function sums the inventory level values in the corresponding rows 6 and 8 of column B, which are the cells B6 and B8 values. Hence, the function returns 850 as the required total.

Example #3

The dataset includes the date-wise total shares purchased data of a company.

We must find the total shares purchased in the date range mentioned in cells B10:B11 while including the date range limits. Let us take cell B13 as the target cell.

Then, fetch the required output using the dates-criteria-based SUMIFS function in the target cell.

Step 1: Select cell B13, enter the following SUMIFS(), and press Enter.

=SUMIFS(B2:B7,A2:A7,”>=3/4/2024″,A2:A7,”<=3/21/2024″)

First, the SUMIFS() examines cells in cells A2:A7 containing dates after or equal to the cited beginning date. Cells A3:A5 meet the first condition. After that, the function verifies the second criterion, which is checking for cells in rows 3 to 5 of column A holding dates before or equal to the cited last date. Cells A3:A5 satisfy the second condition.

So, the function sums the shares purchased values in the corresponding rows 3, 4, and 5 of column B, which are the cells B3, B4, and B5 values. Therefore, the function returns 460 as the required total figure.

Important Things To Note

The numeric data and date values in the SUMIFS with dates formula should adhere to the Excel-recognized number and date formats to avert critical errors.
Enter the appropriate logical operators in the dates criteria-based SUMIFS function according to whether to consider or keep out the date range limits from the evaluation.
The sum_range and date_criteria_range lengths supplied as input to the SUMIFS function should be the same to avoid the function returning the #VALUE! Excel error value.

Frequently Asked Questions (FAQs)

1. Does SUMIFS work with dates?

SUMIFS works with dates when the dates are specified as criteria arguments in the function to sum the required values.

2. How to use SUMIFS with dates VBA formula?

We can use SUMIFS with dates VBA formula by following the steps below, described with an illustration.

The first dataset lists the date-wise order quantities, and the second contains the date range limits.

We aim to evaluate the total order quantity in the cited date range and showcase the outcome in cell B13.

Then, the steps to utilize a SUMIFS()-based Excel VBA code to fetch the required outcome are as follows:

Step 1: The sheet having the source datasets should be the current sheet, and use Alt + F11 to open the VBA Editor.

Step 2: Select the current VBAProject. Next, choose Module from the Insert tab.

The above step will open a blank module window.

Step 3: Feed the required code in the empty module to apply the required dates criteria-based SUMIFS function in the specified cell.

Step 4: Select the play option to execute the VBA code.

Finally, access the current sheet to secure the required output in cell B13.

The function first identifies cells among the cells B2:B7, holding dates after the beginning date, followed by before or equal to the ending date, which are cells B5:B7.

So, the function sums the numbers in rows 5 to 7 of column C, which are the cells C5:C7 values, to return 610 as the required total order quantity in the cited date range.

3. Why is SUMIFS with dates not working?

The SUMIFS with dates is not working, perhaps because of the following reasons:

• The supplied dates and numeric argument values have incorrect date and number formats.
• The logical operators entered in the SUMIFS function are incorrect.
• The sum_range and date_criteria_range are of different sizes.

Download Template

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

SUMIFS-with-Dates-Excel-Template Download

SUMIFS With Dates