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)
210 comments
HI, I hope you can help me.
What formula to use if I need to get the total amount received, for example, date range is from March 18-30, or April 1-15 and multiply it by 500.
Thanks.
Hello!
Use the SUMIFS function to calculate the sum by condition. Please have a look at this article above.
This should solve your task.
Hi All,
I have a small challenge which I am facing.
I have following columns in my sheet
1. Project ID
2. Business Unit (BU)
3. Start Date
4. End Date
I have an ask here to show the count for all Active projects
My data needs to be
BU1 BU2 BU3
2022
JAN 10 7 14
FEB 13 6 11
MAR and likewise for all other below months
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
AND LIKEWISE FOR 2023 TILL ALL YEAR END date where Active Project means that has a start date = the month and year.
In above example at a glance I should be able to see how many projects are active in 2022, how many are active in Jan, Feb etc, how many are active in future years as well till my last project ends
can someone help?
Hello!
Please check out this article to learn how to count cells based on several conditions. I don't know how your dates are written, so I recommend instructions on how to count the values depending on the date.
11/25/2021 $1,506,500.00
12/2/2021 $39,854.50 $1,466,645.50
12/9/2021 $39,854.50 $1,426,791.00
12/16/2021 $39,854.50 $1,386,936.50
12/23/2021 $39,854.50 $1,347,082.00
12/30/2021 $39,854.50 $1,307,227.50
I have a sheet with a big bill. Payments are being remitted weekly. My sheet has a date column for the payment date. Payment amount. Remaining balance as of that day.
I'm trying to get a updated balance for this sheet in another sheet.
How can I have the balance update only weekly in another sheet?
Hello!
You can use XLOOKUP function to find the last match for a given date. See an example here: XLOOKUP last match.
I hope I answered your question. If something is still unclear, please feel free to ask.
Hi, I wish to calculate a Years of Service in employment minus a period of absence.
eg: date of joining: 01MAR12
years of service end date for calculation: 31DEC21
period of absence: 03OCT16 to 02OCT17
I can calculate the Years of Service range but unable to deduct the period of absence in the same formula - is that possible?
Hello!
I don't know what formula you are using. Calculate the number of years from 01MAR12 to 03OCT16 and the number of years from 02OCT17 to 31DEC21. Then count their sum.
Ok then, but it did not work to just count the Sum, as I want the results to be Years, Months, Days format (using DATEIF formula). Using my example dates above, how would I add in excel two different periods: 01MAR12-03OCT16 with 03OCT17-31DEC21, so the final answer is in Years, Months, Days, as you can't add results of two DATEIF answers to get the final answer.
Hello!
In the DATEDIF function, you can only use one date to convert days to year - month - day.
=DATEDIF(0, A2, "y") & " year " & DATEDIF(0, A2, "ym") & " month " & DATEDIF(0, A2, "md") & " days"
Count the number of days, as I recommended to you earlier, and then apply this formula.
Thanks for an interesting topic. Personally, I'm facing this problem, but with an added dimension:
2019 2020 2021 2022
January 1,600 1,780 1,860 1,890
Feb 1,600 1,780 1,860 1,890
March 1,600 1,780 1,860 1,890
April 1,780 1,860 1,890 1,890
May 1,780 1,860 1,890
June 1,780 1,860 1,890
July 1,780 1,860 1,890
August 1,780 1,860 1,890
Sept 1,780 1,860 1,890
October 1,780 1,860 1,890
Nov 1,780 1,860 1,890
Dec 1,780 1,860 1,890
I want to generate a sum, say, between two dates the user inputs on a separate sheet:
Start Date 01/10/2019
End Date 01/03/2021
Total Amount 22,080
Which is the most efficient formula to use and automatically pick up the relevant reference(s) and getting the sum...without going VBA.
Hi!
If the months in column A are written in numbers (1,2,3 ... 12), then the amount can be calculated using the formula:
=SUM(B2:B13*(DATE(B$1,$A2:$A13,1)>=$F$1)*(DATE(B$1,$A2:$A13,1)<=$F$2))+SUM(C2:C13*(DATE(C$1,$A2:$A13,1)>=$F$1)*(DATE(C$1,$A2:$A13,1)<=$F$2))+SUM(D2:D13*(DATE(D$1,$A2:$A13,1)>=$F$1)*(DATE(D$1,$A2:$A13,1)<=$F$2))
F1 and F2 - start and end date.
I'm not sure if I understood you correctly, as I do not understand how the number 22080 is calculated.
Hi
I have a problem how to sum folowing
Artikel Date free i
630196 10/03/2021 2
630196 10/03/2021 8
630196 10/03/2021 6
630196 31/03/2021 0
630196 21/06/2021 0
630196 13/07/2021 8
630196 13/07/2021 2
630196 13/07/2021 12
630196 13/07/2021 6
630196 10/08/2021 0
630196 12/08/2021 0
630196 08/09/2021 40
I neet to do the sum for each row(in my case material code 630196) that is lover than date 19/11/2021
Means in first row 2 in second 10 and so one
Pleas help because I try do do this for two days bu no results
Milan
Hello!
To find the sum for multiple conditions, use the SUMPRODUCT function:
=SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2
Thanks so much, I really learnt how to use SUMIFS formula and it helped me to master my cost tracker.
thanks again and best regards
Madjid
I am needing assistance on how to sum two dates to a count.
For example in cell M16, I have a start date of 6/11/2021 and in cell N16, I have the end date of 6/30/2021 and now I need to count the amount of days from start to end in cell O16. Can someone assist with me the formula to be able to do that?
Hi!
Check out this guide: Calculate difference between two dates in days, weeks, months or years
It contains answers to your question.
I have a very similar formula referencing raw data on another sheet, I have created a date column for normalized dates for this function to reference however it keeps doing total sum for all raw data and is not taking into account the date ranges at all. I am trying to reference fields for rolling 12 month calculations on several different sheets of data.
I have confirmed all dates are formatted as dates, used the same normalization formula on another sheet and it is working.
SUMIFS('SFDC Closed Opp Export'!L:L,'SFDC Closed Opp Export'!B:B, ">="&A1, 'SFDC Closed Opp Export'!L:L, "<="&B1, 'SFDC Closed Opp Export'!V:V, A6)
Column L on Raw data is number
Column B is the normalized date for close date
column V is the segment
Hello!
Unfortunately, your problem is not clear to me. The formula contains links to your worksheets and is therefore difficult to understand. Give an example of the initial data and the desired result.
I actually figured it out - thank you
Data contain the date with time (actual transaction time is capturing) eg; 29-08-2021 2:36:27 PM, 29-08-2021 2:53:17 PM like wise,
want to sumif to date wise only means date of 29-08-2021 , how could formulate it
Great, I have used to determine the bank's balance in my accounts for a given date, so now I can sum by the debit and credits columns for all data less or equal than the current date and compare with the real data in bank's account and both data in a different column for each daily date.
Accounting Balance = SUMIFS(debits,date,"<="&given-date,Bank,"Citibank")-SUMIFS(credits,date,"<="&given-date,Bank,"Citibank")
I appreciate your post, thank you very much. Simple and effective.
Hi, I'm using a sumifs function for each month's totals with the year referenced in a separate sheet. It works great for every month except Feb/March. I used the range "="&DATE(DASHBOARD!$C$2,2,1),$B:$B,"<="&DATE(DASHBOARD!$C$2,2,29))
Not sure why half my message is cut off. For Feb Entries I allowed "<=" 2-29 to allow for leap years but now entries for Mar 1 show up in BOTH Feb. and Mar. totals. How do I include all dates between the beginning and end of each month allowing for leap year without problems? Thanks.
Hello!
To determine the end date of the month, use the function EOMONTH :
=EOMONTH(DATE(DASHBOARD!$C$2,2,1),0)
Read more here.
Hello,
I am sincerely hoping you can help me. I'm trying to find a formula to add in monthly rent on the first day of the month to an equation which will then subtract it from the amount that they paid that month to give me a running total of the amount that they owe
Rent Due Running Total January Febraury
of Money Owed
1000 ?? 850 900
I have a cash flow on one sheet of an excel spreadsheet with a balance for each month of the year. each month aligns to UK tax year so April runs from the 6th of April to the 5th of May and so on. Using The TODAY() function I am trying to the show the current monthly balance from the cashflow on another sheet.
Can you assist?
Hello!
If I understand your task correctly, the following formula should work for you:
=SUMPRODUCT(D1:D15,--(C1:C15>=EOMONTH(TODAY(),-1)+6),--(C1:C15<=EOMONTH(TODAY(),0)+5))
You can learn more about EOMONTH function in Excel in this article on our blog.
I am trying to do a sumifs formula on a range of dates, however It keeps returning a zero
05 May 2020 14,256.00
01 May 2020 12,356.00
04 May 2020 12,548.00
02 May 2020 12,536.00
06 June 2020 12,586.00
08 June 2020 12,365.00
10 May 2020 12,356.00
Find Data between ranges: 01 May 2020 - 10 May 2020
=SUMIFS(C2:C8,B2:B8,">''&F2",B2:B8,"<''&G2")
Hello!
The formula below will do the trick for you:
=SUMIFS(C1:C8,B1:B8,">"&F2,B1:B8,"<"&G2) I hope it’ll be helpful.
It says that there is a problem with this formula
It would be great to get some help if possible. I have a spreadsheet which tracks transactions and values between certain dates, and I am trying to do a sum dependent on a certain date criteria i.e how much value was gained in the month of April.
I followed your steps, and have a formula of:
=SUMIFS('EXP April-June 2021'!AF2:AF5131,'EXP April-June 2021'!I2:I5131,">="&DATE(2021,4,1),'EXP April-June 2021'!AF2:AF5131,"<="&DATE(2021,4,31))
And it returns a figure for me, but it's about £10m short of what it should be when I use the highlight & count approach on those dates.
I cannot work out why it would be doing this. All of the dates are formatted as dates, and the values are formatted as currencies.
I have tested this on the results for May & June so far, and the formula works in that it returns a different figure for each month, and updates as I add more to the source data, but doesn't calculate the correct figure currently.
Nevermind... I fixed it. I accidentally put the value data set in the formula twice rather than the date data set. Thanks for your guides!
Hello!
I can't see your data but I guess the problem is in DATE(2021,4,31). No such date exists. The formula DATE(2021,5,31) returns May 31, 2021 00:00:00. Therefore, the last day of the month is ignored. Use DATE(2021,6,1) instead of DATE(2021,5,31). Perhaps this will help.
S.NO DATE AMOUNT
1 19-04-2021 -8,930.00
2 20-04-2021 20,250.00
3 22-04-2021 7,047.20
4 23-04-2021 -27,000.00
5 26-04-2021 -4,200.00
6 27-04-2021 -
7 28-04-2021 10,500.00
8 29-04-2021 -12,000.00
9 30-04-2021 -3,200.00
How to calculate total -ve value of every month; +ve value of particular month & count for zero by sumifs & countif functions.
Please help me. I am waiting for your solution.
Thanks
prakash
Hello,
I'm having right trouble with this formula. I can't work out what i've done wrong! I'm trying to calculate price by customer within a set date period.
=SUMIFS(G8:G20, B8:B20, "=>"&B5, B8:B20, "=<"&C5, M8:M20, "="&A5)
Can someone identify what's wrong with it?
Column G is price
Column B is date
Column M is customer
A5 is customer name
B5 is the start date
C5 is the end date
I've checked the dates are in date format and the numbers are in number format
I've interchanged the signs
I've added and removed the "="& for the customer name
I've added spaces and removed them.
None of these have resulted in anything. The Formula box remains blank.
Help please!!
Hello!
Here is the article that may be helpful to you: How to sum if between two dates in Excel.
=SUMIFS(G8:G20, B8:B20, ">="&$B$5, B8:B20, "<="&C5, M8:M20, "="&$A$5)
“=>” is wrong.
I'm not sure if the summation of prices makes sense.
I'm trying to summarize some product counts by different date ranges. For example, in column C, I have several products listed from rows 13 thru 50 with some products in a similar category repeating and the counts are in columns to the right with dates as headings (ie: 6/28/21, 6/29/21, thru 12/31/21). I need to summarize by product and then by different date ranges (ie: weekly vs monthly) but am not sure the best way to do this in Excel. All the examples I see have all the data in columns including the dates, but in my case I don't have that.
GL Acct 06/28/21 06/29/21 06/30/21 7/01/21 07/02/21 07/03/21....
Filling 1 10 15 12 5 6 4
Filling 2 4 1 3 20 11 2
So, I need to be able to sum the Fillings into 1 number for Jun that includes the numbers from 6/28 and 6/29 and then summarize the Fillings into 1 total for Jul that includes 7/1 thru 7/3.
Any help is appreciated.
Kat
Hello!
Take a look at this comment. Perhaps this is the answer to your question.
If something is still unclear, please feel free to ask.