SUMIFS Date Range: The Sum Between Multiple Dates

Whenever you work with datasets that include dates like SUMIFS Date Range, you may often see yourself attempting to do calculations according to these dates.

For example, when you have the monthly sales information, it can be possible for you to want to become aware of the total sales with a specific period or the total sales that happened during the weekdays compared to the weekends.

If you want to get the answers quickly, use the SUMIFS function.

Just as the name suggests, the SUMIFS function lets you sum up a range based on specific criteria.

SUMIFS allows you to specify various conditions to only sum up the value of the cells that can meet every requirement.

Our tutorial will show you how you can have values summed up between two specific dates using the function SUMIFS.

Let’s begin!

Whenever you work on an investment plan, report, or any other data set that includes dates, you may frequently have to sum up the numbers in a specified period.

Our tutorial will help you learn about a great solution: the inclusion or the date range as criteria in the SUMIFS formula.

In Excel forums and blogs, people frequently ask how to use SUMIF to get the date range.

If you want to get the sum between one date to another, both dates need to get defined, while the function Excel SUMIF has only one condition allowed.

However, we are lucky as we can use the SUMIFS function capable of supporting multiple criteria.

Within a dynamic range, get the sum that is based on the date today.

Here, you have to sum up the data within a dynamic date range (Y days forward or X days back from now).

Using the function TODAY, you can construct the criteria that will allow you to get the date now to get updated automatically. 

As an example, if you want to sum the budgets due in the past week that includes the date today, this will be the formula:

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

Whenever you don’t want to include the date now in the final result, use the operator less than (<) as the first criteria that will not include the date today.

Then, use the greater than or equal to (>=) as the second criteria.

Here, the date that is seven days prior to now is included: =SUMIFS(B2:B10, C2:C10, “<“&TODAY(), C2:C10, “>=”&TODAY()-7)

In the same way, you can have the values summed up whenever the date has a given number of upcoming days.

As an example, you can get the total budget due in the next five days by using these formulas:

Use this if you don’t want the result to include the date today:

=SUMIFS(B2:B10, C2:C10, “>”&TODAY(), C2:C10, “<=”&TODAY()+3)

Here is what you should use if you want to include today’s date in the result:

=SUMIFS(B2:B10, C2:C10, “>=”&TODAY(), C2:C10, “<“&TODAY()+3)

How to use Excel SUMIFS to sum up all values between two dates

If you want to have values summed up in a specific date range, you can have the SUMIFS formula used with the beginning and end dates included in the criteria.

The SUMIFS function has a syntax that requires people to initially specify the values they want to add up (sum_range) and give the criteria or range pairs.

Here, the range or list of dates for both requirements will be the same.

Considering this information, the generic formula you can use to sum the values between a couple of dates will be in this form:

Excluding the set dates:

SUMIFS(sum_rangedates,”>”&start_datedates, “<“&end_date)

Including the bound dates:

SUMIFS(sum_rangedates,”>=”&start_datedates, “<=”&end_date)

They are only different because of the logical operators.

The first formula checks if the date is less than (<) or greater than (>) and leaves out the beginning and end dates.

The second formula uses less than or equal to (<=) and greater than or equal to (>=), so the result will include the upper and lower set dates.

Below is a table where you can suppose that you want to have the projects summed up due in a specific date range.

You can do this by entering the end date in G1 and the start date in F1.

Then, you can use this formula to have the budgets added up between two dates from B2:B10 that is inclusive:

=SUMIFS(B2:B10, C2:C10, “>=”&F1, C2:C10, “<=”&G1)

Whenever you want to do hardcoding on the formula’s date range, have the date typed in after the logical operator so you can have the whole criteria enclosed in quotation marks:

=SUMIFS(B2:B10, C2:C10, “>=9/10/2020”, C2:C10, “<=9/20/2020”)

If you want to avoid any possible mistakes, have the dates supplied with the assistance of the DATE function:

=SUMIFS(B2:B10, C2:C10, “>=”&DATE(2020,9,10), C2:C10, “<=”&DATE(2020,9,20))

In the above formula, you may use any valid format date. As an example, in the cells, I was able to use the date 01-01-2020.

However, in the formula, it can be possible for me to use whatever format to refer to this date.

For example, I can have 1-Jan-2020, 01 Jan, 2020, or 01 January 2020 used as long as it has a valid date format. Excel can still use any of them if you want to calculate the sum between two given dates.

Just remember that the operator has to be in double-quotes, and the cell reference must not be within those double-quotes.

Date criteria syntax SUMIFS

Whenever people use dates as the criteria for the Excel functions SUMIF and SUMIFS, anyone can feel confused.

However, once you look at it closely, every variety of the use cases could boil down to some simple rules:

Whenever you input a date in a predefined cell, you can provide criteria that come in the form of a text string.

It uses quotation marks to have a logical operator enclose to begin a string and have the & or ampersand used to concatenate and have the string finished off.

As an example:

=SUMIFS(B2:B10, C2:C10, “>=”&F1, C2:C10, “<=”&G1)

Whenever another function like TODAY or DATE () drives the date, have the function and comparison operator concatenated.

Here is an example:

=SUMIFS(B2:B10, C2:C10, “>=”&DATE(2020,9,10), C2:C10, “<=”&TODAY())

In case you directly put the dates in the criteria arguments, have a logical operator typed (<, >, <>, =) before the date.

Then, have the whole criteria enclosed in quotes.

Check out this example:

=SUMIFS(B2:B10, C2:C10, “>=9/10/2020”, C2:C10, “<=9/20/2020”)

Use SUMIFS with other criteria and between two dates

Since there are multiple conditions you can use with the SUMIFS function, you can include additional criteria in the formula.

Just add a criteria pair or range to your SUMIFS formula if you want to sum values that meet other conditions in another column within a date range.

As an example, you can sum up the budgets in a specific date range for every project with “high” in their names.

Just have the formula extended with wildcard criteria:

=SUMIFS(B2:B10, C2:C10, “>=”&F1, C2:C10, “<=”&G1, A2:A10, “high*”)

Here, the project names are A2:A10, the numbers to sum are B2:B10, the dates to check are C2:C10, the end date is G1 while the start date is F1.

Aside from that, nothing can prevent you from having a third criterion entered in another cell. Here is a screenshot that shows the referencing of that cell:

You can have this done when you include another condition in the formula.

Here, other than checking the date, it also reviews if the product is a Printer or not.

It will then provide a result that can match every given condition.

Here is the formula that can provide the answer to this:

=SUMIFS(C2:C15,A2:A15,”>=1-1-2020″,$A$2:$A$15,”<=31-01-2020″,$B$2:$B$15,”Printer”)

This formula will check if the product if it’s a Printer and also reviews the dates. It will only have the value summed up when it meets all three conditions.

In the same way, you can also have the sum of the values from two dates where you want to have a specific product excluded.

As an example, you may want to have the values from 1 to 31 Jan summed up for al products except for the scanner.

Here is the formula you can use for this:

 =SUMIFS(C2:C15,A2:A15,”>=1-1-2020″,$A$2:$A$15,”<=31-01-2020″,$B$2:$B$15,”<>Scanner”)

The not-equal-to-operator (<>) is used in the formula above to have the values for Scanner excluded in the result.

In reiteration to this, the formula has hard-coded data values. However, once you have the dates in the cell, you can use the formula if you want to refer to that cell.

These are great examples where you can have values between two dates summed up.

You can have that formula tweaked to include additional conditions whenever you want to.

Excel’s SUMIFS between dates don’t work.

If you have a formula that produces incorrect results or doesn’t work, these are the troubleshooting tips that you need to follow so you can shed light on why it fails to help you fix any issues.

For criteria, use the correct syntax.

Whenever you use SUMIFS to check the dates, you have to put the date in quotation marks similar to this: “>=9/10/2020”; functions and cell references and functions need to get placed outside the quotes that they appear like this “<=”&G1 or “<=”&TODAY(). 

Verify the logic of the formula

Whenever the budget has even just a minor type, it could cost you millions.

Whenever there is a mistake in your formula, you may have to debug it for hours.

Because of that, whenever you sum between a couple of dates, you need to do these things.

First, make sure to check if the beginning date is preceded by the operator greater than or equal to (>=)or greater than (>).

Next, review if the end date has been prefixed by less than or equal to (<=) or less than (<).

Make sure that every range has the same size.

If you want the SUMIFS function to work the right way, the criteria ranges and sum range should have equal size, or else there will be a #VALUE! Error.

Fix this by ensuring that every criteria_range argument contains the same number of columns and has the sum_range.

Review the format of numbers and dates

Whenever the seemingly right SUMIFS formula only returns zero, what you need to do first is to check if your dates are dates and not just text strings that appear similar to dates.

After that, review if numbers are what you are summing up and not just numbers stored as text.

We hope that we helped you use the Excel SUMIFS function in summing up the data in a date range!

In case you have other great solutions in mind, please share them with us in the comments.

Thanks for checking out our article.

We hope to see you again next week!

Discover How to Use Excel’s SUMIFS Date Range Between Multiple Dates with ExcelMaster

Make yourself an Excel expert with us! Allow yourself to become sufficiently knowledgeable on how to use sum if between multiple dates using Excel’s Sumifs date range with ExcelMaster NOW!