by Working on a report, investment plan or any other dataset with dates, you may often need to sum numbers within a specific period. This tutorial will teach you a quick and easy solution - SUMIFS formula with date range as criteria.
On our blog and other Excel forums, people often ask how to use SUMIF for date range. The point is that to sum between two dates, you need to define both dates while the Excel SUMIF function only allows one condition. Luckily, we also have the SUMIFS function that supports multiple criteria.
How to sum if between two dates in Excel
To sum values within a certain date range, use a SUMIFS formula with start and end dates as criteria. The syntax of the SUMIFS function requires that you first specify the values to add up (sum_range), and then provide range/criteria pairs. In our case, the range (a list of dates) will be the same for both criteria.
Considering the above, the generic formulas to sum values between two dates take this form:
Including the threshold dates:
Excluding the threshold dates:
As you can see, the difference is only in the logical operators. In the first formula, we use greater than or equal to (>=) and less than or equal to (<=) to include the threshold dates in the result. The second formula checks if a date is greater than (>) or less than (<), leaving out the start and end dates.
In the table below, suppose you want to sum projects that are due in a particular date range, inclusive. To have it done, use this formula:
=SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "<=9/20/2020")
If you'd rather not hardcode a date range in the formula, then you can type the start date in F1, the end date in G1, concatenate the logical operators and cell references and enclose the whole criteria in quotation marks like this:
=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)
To avoid possible mistakes, you can supply dates with the help of the DATE function:
=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "<="&DATE(2020,9,20))
Sum within a dynamic range based on today's date
In situation when you need to sum data within a dynamic date range (X days back from today or Y days forward), construct the criteria by using the TODAY function, which will get the current date and update it automatically.
For example, to sum budgets that are due in the last 7 days including todays' date, the formula is:
=SUMIFS(B2:B10, C2:C10, "<="&TODAY(), C2:C10, ">"&TODAY()-7)
If you'd rather not include the current date in the final result, use the less than operator (<) for the first criteria to exclude today's date and greater than or equal to (>=) for the second criteria to include the date which is 7 days before today:
=SUMIFS(B2:B10, C2:C10, "<"&TODAY(), C2:C10, ">="&TODAY()-7)
In a similar manner, you can sum values if a date is a given number of days forward.
For example, to get a total of budgets that are due in the next 3 days, use one of the following formulas:
Today's date is included in the result:
=SUMIFS(B2:B10, C2:C10, ">="&TODAY(), C2:C10, "<"&TODAY()+3)
Today's date is not included in the result:
=SUMIFS(B2:B10, C2:C10, ">"&TODAY(), C2:C10, "<="&TODAY()+3)
Sum if between two dates and another criteria
To sum values within a date range that meet some other condition in a different column, simply add one more range/criteria pair to your SUMIFS formula.
For example, to sum budgets within a certain date range for all the projects that contain "tip" in their names, extend the formula with a wildcard criteria:
=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1, A2:A10, "tip*")
Where A2:A10 are the project names, B2:B10 are the numbers to sum, C2:C10 are the dates to check, F1 is the start date and G1 is the end date.
Of course, nothing prevents you from entering the third criteria in a separate cell too, and referencing that cell like shown in the screenshot:
SUMIFS date criteria syntax
When it comes to using dates as criteria for Excel SUMIF and SUMIFS functions, you wouldn't be the first person to get confused :)
Upon a closer look, however, all the variety of use cases boils down to a few simple rules:
If you put dates directly in the criteria arguments, then type a logical operator (>, <, =, <>) right before the date and enclose the whole criteria in quotes. For example:
=SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "<=9/20/2020")
When a date is input in a predefined cell, provide criteria in the form of a text string: enclose a logical operator in quotation marks to start a string and use an ampersand (&) to concatenate and finish the string off. For instance:
=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)
When a date is driven by another function such as DATE or TODAY(), concatenate a comparison operator and a function. For example:
=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "<="&TODAY())
Excel SUMIFS between dates not working
In case your formula is not working or producing wrong results, the following troubleshooting tips may shed light on why it fails and help you fix the issue.
Check the format of dates and numbers
If a seemingly correct SUMIFS formula returns nothing but zero, the first thing to check is that your dates are really dates, and not text strings that only look like dates. Next, make certain you are summing numbers, and not numbers stored as text. The following tutorials will help you spot and fix these issues.
Use the correct syntax for criteria
When checking dates using SUMIFS, a date should be put inside the quotation marks like ">=9/10/2020"; cell references and functions should be placed outside the quotes like "<="&G1 or "<="&TODAY(). For full details, please see date criteria syntax.
Verify the formula's logic
A small typo in a budget could cost millions. A little mistake in a formula might cost hours of debugging time. So, when summing between 2 dates, check if the start date is preceded by the greater than (>) or greater than or equal to (>=) operator and the end date is prefixed by less than (<) or less than or equal to (<=).
Make sure all ranges are the same size
For the SUMIFS function to work correctly, the sum range and criteria ranges should be equally sized, otherwise a #VALUE! error occurs. To fix it, ensure that all criteria_range arguments have the same number of rows and columns as sum_range.
That's how to use the Excel SUMIFS function to sum data in a date range. If you have some other interesting solutions in mind, I'll be really grateful if you share in comments. Thank you for reading and hope to see you on our blog next week!
Practice workbook for download
SUMIFS date range examples (.xlsx file)
209 comments
Hi! You can see the correct SUMIFS formula with dates in the first section of the article above: How to sum if between two dates in Excel.
For example:
=SUMIFS($D$18:$D$1516,$H$18:$H$1516,">=01/01/2022",$H$18:$H$1516,"<=12/31/2022")
You can also find useful information in this article: Excel SUMIFS and SUMIF with multiple criteria – formula examples.
With much help from this board - i was finally able to sum all amounts related to a specific category
Amazon =SUMIF($C$18:$D$1516,G18,$D$18:$D$1516) $481.38
APPLE =SUMIF($C$18:$D$1516,G19,$D$18:$D$1516) 56.38
AUTO GAS =SUMIF($C$18:$D$1516,G20,$D$18:$D$1516) 1662.59
ETC
But now I want to ALSO sum WITHIN a date range....ie all Amazon purchases for 2022.. I input :
=SUMIF(H19,">=01/01/2022"&""<=12/31/2022)
I initially only had one " after the & sign but a window popped up saying (we found a typo in your formula and tried to correct it - do you want to accept this correction..I said yes)...but I still got a -0- result.
What am I doing wrong - what SHOULD the formula be?
Hi,
I have an excel with two tabs, one has the daily report on it with numbers and the 2nd tab needs to capture data weekly, if the month is ending 2 days in a week it has to be 2 days data, if it is 5 days a week then it has to be 5 days a week. Currently i am manually going back and forth to each tab and linking the file. is there any formula to auto sum weekly data from daily tab. please let me knoe
Hi! You can sum data by the number of the month and the number of the week. If the month and week numbers are written to additional columns using the MONTH and WEEKNUM functions, you can use the SUMIFS function. The formula might look like this:
=SUMIFS(B1:B20,E1:E20,18,F1:F20,5)
The best and easiest to follow explanation of using sumif between two dates and another criteria. Thank you, thanks you, thank you :D I've now got a sheet that does exactly what I want it to do.
Thanks
Would appreciate someone's help...
These two SumIf formulas in the same spreadsheet produce different results:
=SUMIFS($AC2:$AC4655,$D2:$D4655,">=06/01/2023 ??:??",$D2:$D4655,"=06/01/2023 ??:??",$D2:$D4655,"<=06/31/2023 ??:??") yields $21,344.95
My question is, since I am not inputting a 6/31 date, why should the sums be different?
Cheers!
Tom
Don't know what happened there. The two formulas should be...
Would appreciate someone's help...
These two SumIf formulas produce different results:
=SUMIFS($AC2:$AC4655,$D2:$D4655,">=06/01/2023 ??:??",$D2:$D4655,"=06/01/2023 ??:??",$D2:$D4655,"<=06/31/2023 ??:??") yields $21,344.95
My question is, since I am not inputting a 6/31 date, why should the sums be different?
It's still screwed up. Not what I am inputting to this replay box.
Hi! I can't check your formula because I don't have your data. Remove the ??:??
Also keep in mind that date 06/01/2023 means 06/01/2023 00:00:00.
All the necessary information is in the article above.
The data in column AC are $ amounts in currency format. The data in column D are dates in text format: 06/01/2023 14:15.
Appreciate your considering the issue!
Looking at my original post I se only one formula managed to get pasted in. Weird. The formula I thought would be proper is:
=SUMIFS($AC2:$AC4655,$D2:$D4655,">=06/01/2023 ??:??",$D2:$D4655,"=06/01/2023 ??:??",$D2:$D4655,"<=06/31/2023 ??:??") yields $21,344.95
Many thanks!
Tom
So I'll try this. The first formula, the one I believed would be correct is:
=SUMIFS($AC2:$AC4655,$D2:$D4655,">=06/01/2023 ??:??",$D2:$D4655,"<=06/30/2023 ??:??"
This yields $20,414.95
Ok, since that posted correctly, here's the second formula, where only end date has changed:
=SUMIFS($AC2:$AC4655,$D2:$D4655,">=06/01/2023 ??:??",$D2:$D4655,"<=06/31/2023 ??:??")
This yields $21,344.95
But I do not have any 06/31/2023 entries in my D column data
Hi! You cannot do any calculations with text strings. Convert text to date in a separate column. To do this, you can use the methods described in this manual: How to convert text to date and number to date in Excel. For example:
=DATEVALUE(LEFT(D2,10))
Then use the data from that column with the dates in the SUMIFS formula.
How do I sum based on a dynamic date range? I'm trying to build a mortgage calculator where the mortgage will commence on a certain day (say today) but clients keep asking me how much will they pay in principal and interest within that calendar year (i.e. Dec-31 20xx). How would I program the SUMIF so that I can get interest and principal outputs for that calendar year (i.e. mortgage starts in any month).
Keep in mind the excel sheet has a row for each month already so I know the IPMT and PPMT for each month, but the summation is where its tricky for me. I'd like to program it so that whichever calendar year we're in (i.e. 2023, 2024, 2025), the SUMIF function would output the summation of the interest paid for that calendar year only
I'm trying to avoid hard coding dates or use the (=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1, A2:A10, "tip*") example above as that would require some manual inputting of dates. It'd be ideal if Excel could pick up the first month (e.g. March, or whichever month the property is closing in) and calculate the interest automatically up until December, and the same thing for the next calendar year, and keep continuing and providing 30 outputs for the 30-year amortization period (the last year will be January to February based on the example above).
Hi! If I understand your task correctly, to automatically get the date of the last day of the current year in cell G1, use the DATE function.
=DATE(YEAR(TODAY()),12,31)
Hope this is what you need.
Hi Alex, thanks for looking into this. Unfortunately, it doesn't answer my question. I'm not trying to output a date but rather a summation of a column based on a dynamic date.
The date range will be dynamic but how do I loop this into the summation formula??
- Basically if the mortgage starts in May 2023 then I'd like to do a summation of my interest and principal columns up until December 2023 (i.e. the first year).
- Then I would like to do a straightforward calculation January to December (for the next 28 years, i.e. 2024 to 2052).
- The last year's calculation would be dynamic once again as it will only be from January to April only (the 30th year i.e. 2053)
I hope the above provides better clarification
Hi! Maybe this article will be helpful: Excel dynamic named range: how to create and use.
Or you can try writing the start and end dates in separate cells and sum the data using the SUMPRODUCT formula as described in this guide: Excel SUMPRODUCT function with multiple criteria.
How to sum product wise 250+ sheets Day wise. Indirect Sumifs within a dynamic range based on today's date
Hi! The SUMIFS formula cannot calculate the sum of data on 250 sheets. I recommend paying attention to Consolidate Sheets tool to calculate the sum on multiple worksheets. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Indirect Sumifs within a dynamic range based on today's date
Hi! As it's currently written, it's hard to tell exactly what you're asking