Excel SUMIF Formula Between Two Dates Using Date Range as CriteriaComprehensive Guide with Screenshots
Having to take the sum between two dates when preparing financial reports or other financial plans is unavoidable.
You don’t have to fret – there is a simple and easy solution to do this in Excel.
This tutorial will focus on the SUMIF formula between different dates in Excel by using Data Range as the criteria.
People often wonder how to use the SUMIF function when it comes to adding dates with different date ranges.
Here’s where people confuse it: you will have to define two dates if you need to take their sum and the SUMIF function only takes into consideration only one condition.
Lucky for you, there are other multiple criteria to support the SUMIF function which we will be discussing in the next sections below:
- Summation Between Two Dates
- Using the SUMIF formula using today as the basis of the date range.
- Using SUMIF Between Another Criterion and Two Excel Dates
- Using SUMIF for Dates Syntax
- Troubleshooting SUMIF Between Not Working Dates
So, let’s begin!
Summation Between Two Dates
Use the SUMIFS formula using start dates and end dates as criteria to get the sum of the data between a range of dates.
Using the SUMIF function syntax, you will first need to provide which values you need to sum up and then proceed to give the range dates.
Given the above, the formula would then become:
The threshold dates included:
=SUMIFS(sum_range,dates,”>=start_date”, dates,“<=end_date”)
The threshold dates excluded:
=SUMIFS(sum_range,dates,”>=start_date”, dates,“<end_date”)
To include or exclude the threshold dates, you will notice that the only difference in the formula is the use of logical operators (<, > or =).
The difference in the exclusion of the threshold date is the “=” sign.
Suppose you have the below data:
If you want to get the sum of a particular expense due within an inclusive date range (May 25, 2021 to Dec 30, 2022), use the formula below:
=SUMIFS(B2:B8, C2:C8, “>=5/25/2021”, C2:C18, “<=12/30/2022”)
There is another way to use the above function using a hardcode of the given date range.
What will you have to do is that the cell references and logical operators need to be concatenated and the entire criteria enclosed in a quotation.
Type the beginning of the date in F1 and the ending date in G1.
The formula to use would then be:
=SUMIFS(B2:B8, C2:C8, “>=”&F1, C2:C8, “<=”&G1)
Now if you wish to avoid any mistakes in the formula, you can use the DATE function as shown below:
=SUMIFS(B2:B8, C2:C8, “>=”&DATE(2021,5,25), C2:C8, “<=”&DATE(2022,12,30))
Using the SUMIF Formula Using Today as the Basis of the Date Range
There will also be times when you wish to know how many days have passed since today, or days fast forward from today.
This can be done by using the TODAY function.
When this function is used, you will be able to get the current date automatically.
Suppose you want to know the expenses that were due in the last 30 days, you may use the formula below:
=SUMIFS(B2:B8, C2:C8, “<“&TODAY(), C2:C8, “>=”&TODAY()-30)
Should you also want to learn the expense due within the next 30 days, you can use the formula as the one shown below:
=SUMIFS(B2:B8, C2:C8, “>=”&TODAY(), C2:C8, “<“&TODAY()+30)
Using SUMIF Between Another Criterion and Two Excel Dates
Assuming you also want to add other criteria to the date range you have provided, this can be done by adding the condition to your SUMIFs formula.
For example, if you also wish to get the same for the expenses that contain “car” in the name, your formula should now look like this:
=SUMIFS(B2:B8, C2:C8, “>=”&F1, C2:C8, “<=”&G1, A2:A8, “car*”)
Where:
B2:B8 – the amounts that are going to be computed to get the sum
C2:C8 – the dates to be checked
F1 – beginning date
G1 – ending date
Using SUMIF for Dates Syntax
There are many people who get confused when using the SUMIF and SUMIFS functions in Excel but here are some rules you would want to remember to avoid getting confused in the future:
- Criteria Arguments
If you include dates within the criteria arguments, you must enter a logical operator (>,=, <, <>) before the dates and quote the entire criteria.
The formula would look like this:
=SUMIFS(B2:B8, C2:C8, “>=5/25/2021”, C2:C10, “<=12/30/2022”)
- Predefined Cell
A text string would now be used if the formula is in a predefined cell.
To do this, the logical operators would be enclosed in quotation marks and then concatenated using ampersand (&) to complete the string.
See below:
=SUMIFS(B2:B8, C2:C8, “>=”&F1, C2:C8, “<=”&G1)
- Another Function
The comparison operator and a function are concatenated when a date is determined by a different function like TODAY() or DATE.
Here’s an example:
=SUMIFS(B2:B8, C2:C8, “>=”&TODAY(), C2:C8, “<“&TODAY()+30)
Troubleshooting SUMIF Between Not Working Dates
If the SUMIF formula is not working, here are a few things that you should check: use the correct formula syntax, verify the logic of the formula, and the same size for all the ranges.
Correct Formula Syntax
What should be noticed here are two things: dates should be placed inside the quotation marks while functions and cell references are placed outside the quotation marks.
See below for examples:
Dates: “>=2/8/2020”
Cell Reference / Function: “<=”&A1 or “<=”&TODAY()
Verify the Logic of the Formula
Here’s one mistake you must always avoid because finding out where you went wrong can cost you more time than making your excel report.
Here’s what you must remember: when writing a formula, the beginning date must always be preceded by the operators greater than (>) or greater than or equal to (>=).
The ending date must also always be preceded by the operators less than (<) or less than or equal to (<=).
Same Size for all the Ranges
A mistake in the formula will give you this error result: #VALUE!.
You can avoid this.
Simply make sure that the sum and criteria ranges are equal in size.
For example, you have to make sure that there are equals rows for the range criteria and sum criteria.