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)
205 comments
I am trying to figure out a formula to count time in hours but the date and time are in the same category which makes it hard to
do the calculation. I need this calculation to determine how long we have had trucks on our yard. Below is an example
Arrived_in_Yard Hit_the_Dock
9/5/2022 16:27 9/8/2022 8:50
How could I calculate this time in hours?
Hello!
The answer to your question can be found in this article: Calculating time difference in Excel (elapsed time).
To find the time difference in hours, multiply by 24.
=(B1-A1)*24
Hi, I have a list of production hours per day for Aug month. 2 columns - Dates & Prod hours. In a separate cell, I need a formula to give me the sum of hours only for Sundays worked. Can you help please? Below is the data set for example:
Dates Prod Hours
Mon 8/1/22 113.68
Tue 8/2/22 104.42
Wed 8/3/22 123.18
Thu 8/4/22 93.24
Fri 8/5/22 84.70
Sat 8/6/22 77.82
Sun 8/7/22 68.29
Mon 8/8/22 116.15
Tue 8/9/22 135.33
Wed 8/10/22 131.12
Thu 8/11/22 99.14
Fri 8/12/22 99.45
Sat 8/13/22 83.66
Sun 8/14/22 75.60
Mon 8/15/22 112.50
Tue 8/16/22 124.28
Wed 8/17/22 131.24
Thu 8/18/22 92.03
Fri 8/19/22 100.12
Sat 8/20/22 75.53
Sun 8/21/22 74.86
Mon 8/22/22 200.38
Tue 8/23/22 179.04
Wed 8/24/22 162.54
Thu 8/25/22 131.83
Fri 8/26/22 130.99
Sat 8/27/22 68.26
Sun 8/28/22 79.09
Mon 8/29/22 111.81
Tue 8/30/22 122.09
Wed 8/31/22 124.22
Thank you
Hello!
To determine the day of the week, use the WEEKDAY function. Use the SUMPRODUCT function to find the sum by condition.
I believe the following formula will help you solve your task:
=SUMPRODUCT(--(WEEKDAY(A1:A20,2)=7),B1:B20)
I'd like to sum up the quantity of one item in a month that is produced in yesterday and today, and I have this table below. Is that possible to use SUMIFS function? Let says today is 08/04/2022 and I want to sum up quantity of item B in today and yesterday.
Item 8/1/2022 8/2/2022 8/3/2022 8/4/2022 8/5/2022 8/6/2022 8/7/2022 8/8/2022
A
B 14000 14000 14560 14000
C
D
E
F 9000 12800
Hello!
Use the MATCH function to find the position of the cell with the current date. Use the OFFSET function to create links to 2 cells. Use these cells to create a condition in the SUMPRODUCT formula.
=SUMPRODUCT(A2:Z2,(A1:Z1=OFFSET($A$1,0,MATCH(TODAY(),$A$1:$Z$1,0)-2))+ (A1:Z1=OFFSET($A$1,0,MATCH(TODAY(),$A$1:$Z$1,0)-1)))
Hope this is what you need.
Thanks for your support. That helps. However we have another case, let says there are few items in the table are duplicated, e.g 2 item A, 4 item B, 5 item C, each of them in a row. So which function need to use to sum up quantity of those items which have been produced yesterday and today?
Hi!
Add a row to your spreadsheet that will calculate the sum for each column. Use the formula I gave you with this total row.
Dates Sale
15-Jan-13 9
15-Jan-13 2
6-Oct-14 7
16-Nov-09 8
18-Jun-13 8
Output should be
Month Sale
Jan 11
Feb 0
Mar 0
Apr 0
May 0
Jun 8
Jul 0
Aug 0
Sep 0
Oct 7
Nov 8
Dec 0
Hi!
Look for the example formulas here: Excel SUMPRODUCT function with multiple criteria - formula examples.
You can use this formula:
=SUMPRODUCT(--(MONTH(A2:A10)=1),B2:B10)
Hi guys,
I am trying to do a =SUMIFS with a DATE RANGE and a wild card (notsure if thats the correct term)
This is the formula that I am working with but it is either going "error" or just "zero".
The date format from the data sheet is "10/16/2021 15:56:34"
=SUMIFS('TDJ Sales Data'!$G$3:$G,'TDJ Sales Data'!$F$3:$F,">="&C1,'TDJ Sales Data'!$F$3:$F,"<="&C2"),'TDJ Sales Data'!$A$3:$A,"Armstrong"")
Hi!
I can't check the formula that contains unique references to your workbook worksheets.
I didn't find a situation where A3:A works (in Excel functions).
If you want to refer to a range starting from A3 to the max row you can use this formula
OFFSET(A3, 0, 0, ROWS(A:A)-2)
Read more here: OFFSET formula to define an Excel dynamic named range.
For example,
=SUMIFS(OFFSET(A3, 0, 0, ROWS(A:A)-2),OFFSET(A3, 0, 0, ROWS(A:A)-2)," > "&C1)
I have a spreadsheet for payroll where I have people who worked multiple shifts and I would like a column with a total for both shifts if there are 2. This is my lay out:
Employee name (which would have to match), Date worked(this will have to match as well), hours for that shift (this is what I want to combine if possible)
Hello!
If I understand correctly, you have two criteria for sum. Try using the instructions and examples in this article: Excel SUMIFS and SUMIF with multiple criteria.
I hope this will help, otherwise don't hesitate to ask.
Good afternoon,
I have two columns. 1 column with the date (25-04-2022 to 30-06-2022) and 1 column with a quantity. I want to add up the quantity per date. For example, for the date 25-4-2022, there are 20 columns with a quantity. How do I get an overview of the total quantities per date? I want to do this because I want to use this data to make a graph of the number of sales per day.
As an example:
Date; Quantity
25-04 -2022; 3
25-04-2022; 4
25-04-2022; 8
26-04-2022; 3
26-04-2022; 6
26-04-2022; 5
27-04-2022; 3
27-04-2022; 8
27-04-2022; 3
etcc
Hi!
Have you tried the ways described in this blog post?
Try the following formula:
=SUMIFS(B2:B10, A2:A10, "="&DATE(2022,4,25))
Use the DATE function to set the date you want. Or use a cell reference with a date.
Hello and thank you in advance!
I am trying to simply to add recorded hours for the week from Sunday-Saturday. The data exists in two simple columns, date in one, hours in the other. But, the days worked vary from week to week (sometimes a few days, sometimes all 7, hence, there will be varying numbers of rows between Saturdays). I would like a formula that finds "Saturday" in column A, then sums the hours for that calendar week (ie, that Sat and the preceding six days). Here's a sample three weeks of that data:
Monday, March 18, 2019 10.03
Tuesday, March 19, 2019 9
Thursday, March 21, 2019 17.38
Friday, March 22, 2019 9.72
Saturday, March 23, 2019 5.52
Monday, March 25, 2019 10.47
Friday, March 29, 2019 9.28
Saturday, March 30, 2019 9.47
Sunday, March 31, 2019 11.45
Monday, April 1, 2019 11.37
Tuesday, April 2, 2019. 11.03
Saturday, April 6, 2019 5.95
The problem is compounded by the fact that not every Saturday is a workday, so in some weeks the last day worked in a calendar week might be a Thursday to Friday.
Hello!
Use the WEEKNUM function to determine the number of a week that begins on Sunday. Write the formula in column C :
=WEEKNUM(A1,1)
Use the SUMIF function to calculate your weekly total. Enter the following formula in cell D1 and then copy it down along the column:
=IF(WEEKDAY(A1,2)=6,SUMPRODUCT(--($C$1:$C$20=WEEKNUM(A1,1)),$B$1:$B$20),"")
You can add subtotals for column C to your table.
I hope this will help, otherwise don't hesitate to ask.
Hi,
I'm using this for a personal month budget and I noticed that when I put in a month's date range, and then enter the next month's entries, it adds it to the previous month.
For instance, let's say I have $1000 of income from 6/1 to 6/30 but when I put in income for July, it adds it to June's total AND July. Is there a way to have it only calculate for the dates in the formula and in between? Here is what I'm using:
=SUMIFS(Input!B:B,Input!A:A,">=6/1/2022",Input!B:B,"<=6/30/2022")
Hello!
If the dates are in column A, then criteria_range2 in the COUNTIFS formula should be column A. You have column B.
That was it! Thank you so much for your response.
If one Date falls between 1st Jan2015 to 31st Dec'15, then if should show as X
1st Jan2016 to 31st Dec'16, then if should show as Y
1st Jan2017 to 31st Dec'17, then if should show as Z
Hello!
For multiple conditions, you can use a nested IF function. Instead multiple IF you can use IFS function.
The year can be calculated using the YEAR function.
=IF(YEAR(A1)=2015,"X",IF(YEAR(A1)=2016,"Y",IF(YEAR(A1)=2017,"Z","")))
=IFS(YEAR(A1)=2015,"X",YEAR(A1)=2016,"Y",YEAR(A1)=2017,"Z")
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.
Hi , I am getting wrong result in Sumif. Below is the data set
B1:x1=Week 49 Week 50 Week 51 Week 52 Week 53 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10 Week 11 Week 12
Weekly jobs done B2:X2 =0 0 0 0 1 0 1 0 2 1 0 1 1 1 0 2 1
Criteria= Week 4
Formula used =SUM(B2:F2)+SUMIF(I1:X1,"<="&A3,I2:X2)
Jobs done till Week 4= 7 result by formula
result should be 4
Thanks in advance
priyank
Hello!
You can use this formula:
=SUM(B2:F2)+SUMIF(G1:X1,"<="&A3,G2:X2)
This formula gives the result 4
Just to say "Thank You! It helps!" our charming Excel wizard! :) .... and to ask "Can you do "AND" logic for two or more conditions with "SUMIF()"?
Hi!
You can learn more about Excel SUMIFS and SUMIF with multiple criteria in this article.
For the test data i'm using the column headers are dates and i'm trying to calculate the sum for the each month, which formula can I use?
I try to get a formula for a calculation. I have expense per month equally Exp:$500 between a range date, Jan 21 - Jun-21. I have some condition, if each expense less then $1000, then Cell A5 will equal to the sum of all expense add up from Jan-21 to Jun-21. but if the expense per month is bigger then $1000, then the Cell A5 will equal to each month of the expense (April 2021).
Col A B C D E F G
Months January-21 February-21 March-21 April-21 May-21 June-21
Expense 500 500 500 500 500 500
Cell A5=April-21
=IF(B3:G3<1000,SUM(B3:G3),SUMIF(B3:G3,B2:G2,A5)) is not working. can you please help me on this?
Hello!
Sorry, I do not fully understand the task. What result do you want to get exactly?
What does the “then the Cell A5 will equal to each month of the expense (April 2021)” phrase mean?
Your question is not entirely clear, please specify.
Hi, I can't get this to work when having the date range in columns. Here's the formula I'm using:
=SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it return 0 even if the = is kept. So it is something with the range config that is not correct.
Can you help?
Note, date format is correct (Scandinavian pc). Resending the post as only part of the text were published.
No point in trying to use this for questions any longer, as the comment function unfortunately doesn't work properly
Hi, I can't get this to work when having the date range in columns. Here's the formula I'm using:
=SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it returns 0 even if the = is kept. So it is something with the range config that is not correct.
Can you help?
Hi,
I cannot validate your formula as it contains unique references. Explain what the expression means v0[@[2021-04-01]:[2021-04-30]] ?
Include an example of the source data and the result you want to get. It’ll help me understand the problem you faced better and help you.
As I been posting, all text is not published. But trying once more, now with the missing part only.
=SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it return.....
no, still not publishing...
=SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, "<="&Report!$C$1)
The sheet (v0) has a table where the headers are dates (the entire April in this case) located in columns C to AF on row 2. Each row in the table contains numbers for each date. The sheet "Report" contains a start date at B1 and an end date at C1.
When using this formula it returns 0 and no error message. As a test, I changed the start/end date to the same date, and changed the formula to only exact match (=), the I get a result for the date in question. But if I add the higher/lower (>,<) signs, it return 0 even if the = is kept. So it is something with the range config that is not correct.
Note, date format is correct (Scandinavian pc).
Hope it makes sense as I had to publish in separate comments
Hello!
Unfortunately, without seeing your data it is impossible to give you advice.
I'm sorry, it is not very clear what result you want to get. Could you please and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.
Hi, I can't get this to work when hvaing the date range in colums. Here's the formula I'm using:
=SUMIFS(v0[@[2021-04-01]:[2021-04-30]], $C$2:$AF$2, ">="&Report!$B$1, $C$2:$AF$2, ",<) signs, it return 0 even if the = is kept. So it is something with the range config that is not correct.
Can you help?
Note, date format is correct (Scandinavian pc).
the comment field is not working, it cuts part of the text...
Hi,
Insert a space after the < sign. Or write this sign in words.
The text was not published in full
Is there any way of using SUMIF combined with days of the week? I have dates which display as the 3-character day of the week (Sun, Mon, Tue, etc.). I'd like to use SUMIF on those days of the week. If I change the date to a 3-character string, then I can make SUMIF work, but I'd rather not.
Amended:
The spreadsheet to include a user defined 'Date A' (ie: 01/15/21), 'Date B' (ie: 04/15/21) and 'Monthly $' (ie: $10.00)
From an invoicing perspective, each day there is a charge for service (prorated amount) based on a monthly amount.
I would like to calculate the dollar amount for a service for 'Date A' through 'Date B' (both Date A & B are billable days).
The spreadsheet to include a user defined 'Date A' (ie: 01/15/21), 'Date B' (ie: 04/16/21) and 'Monthly $' (ie: $10.00)
January there are 17 billable days (01/15/21 thru 01/31/21) * 0.0323 (daily prorate) = $5.48 (prorated amount of the 'Monthly $')
February there are 28 billable days = $10.00 ('Monthly $')
March there are 31 billable days = $10.00 ('Monthly $')
April there are 15 billable days (04/15/21 thru 04/30/21) * 0.5000 (daily prorate) = $5.00 (prorated amount of the 'Monthly $')
A1 = 'Data A'
A2 = 'Date B'
A3 = 'Monthly $'
A4 = ? - hoping for a formula here
Amended:
April there are 15 billable days (04/01/21 thru 04/15/21) * 0.0333 (daily prorate) = $5.00 (prorated amount of the 'Monthly $')
Hello!
If I got you right, the formula below will help you with your task:
=DATEDIF(A1,A2,"m")*A3 + (EOMONTH(A1,0)-A1+1)*A3/(EOMONTH(A1,0)-EOMONTH(A1,-1)) + (A2-EOMONTH(A2,-1))*A3/(EOMONTH(A2,0)-EOMONTH(A2,-1))
You can learn more about DATEDIF function in Excel in this article on our blog.
Hope this is what you need.
I am tracking productivity for a department and want to provide weekly updates to our team in the form of previous week averages. I'm trying to create a quick calculator where I can simply change the Start/End Dates and it recalculates the averages automatically.
The formula I have below keeps returning @DIV/O! errors. C2:KW2 is the portion of the row where daily productivity will be input. C1:KW1 is the row with each days date in it. A36 and A39 are the cells that are dynamic and where I would input the date range I am trying to calculate.
=AVERAGEIFS(C2:KW2, C1:KW1, ">="&A36, C1:KW1, "<="&A39)
Hello!
If there are no dates in the C1: KW1 range that match your conditions, then the formula will return an error. Check how you enter your dates. Perhaps they are written in the form of text.
Sorry about that! I did reread it again and I have attempted a few different things.
let me try by using a simple version of what I would like to accomplish: =SUMIFS(J:J, H:H, ">="&B6, I:I, "<="&B6) H = is my Start Date, I = my End Date, J is the values i would like to sum if they are inside the date listed in B6
My Start date is in one collume and my end date is in another - i cant seem to get this to work?
Example 1: =SUMIFS(Sheet2!$Q:$Q,Sheet2!$I:$I, ">="&Sheet1!B4, Sheet2!$J:$J, "="&1/4/2021,Sheet2!J:J,"<="&1/4/"2021")
Hello!
It is very difficult to understand a formula that contains unique references to your workbook worksheets. I recommend paying attention to the syntax of the SUMIFS function. I kindly ask you to have a closer look at the following paragraph of the article above — SUMIFS date criteria syntax.
Sheet2!$J:$J, "=1/4/2021",Sheet2!J:J,"<=1/4/2021"
I hope it’ll be helpful.
I have question for you as next;
exam: Start dates dest date Year month day
1983-02-07 1989-03-19 6 1 12
1989-04-03 1994-10-08 5 6 5
1994-10-08 2000-12-31 6 2 23
sum() ? ? ?
I want to find and to know it 8 sum of year , months, days by use excel function
Thanks,
Gans
Hello!
Sorry, I do not fully understand the task.
What result do you want to get exactly? Please describe your problem in more detail.
Thank you very much sir!
I would like to do a similar thing but kind of opposite and was wondering if you can help.
We have a spreadsheet with events (multiple days) and numbers of staff e.g.
Start date End date Number of staff
1 Jan 21 3 Jan 21 5
2 Jan 21 2 Jan 21 3
5 Jan 21 10 Jan 21 10
What I would ideally like is to then have a second sheet with all the dates of the year in column A and then how many staff are working on that date so with the above it would look like
1 jan 21 5
2 jan 21 8
3 jan 21 5
4 jan 21
5 jan 21 10
6 Jan 21 10
Etc....
Is that possible?
Thanks
Andy
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
=SUMIFS(Sheet1!$C$1:$C$3,Sheet1!$A$1:$A$3,"<="&Sheet2!A1,Sheet1!$B$1:$B$3,">="&Sheet2!A1)
Hope this is what you need.
I have what I'm assuming is a straightforward formula challenge that I can't seem to figure out.
I have a table of data with the headers "Date,START TIME, END TIME, USAGE, UNITS, COST". The START TIME and END TIME is an hour block of time (e.g., 0:00 & 0:59, up to 23:00 & 23:59).
In adjacent cells I have, say J17 & J18 with a pull down list of dates, that I want to use in a "greater than or equal to 02/01/2021 and less than or equal to 02/28/2021, in a referencing sub table if you will immediately adjacent to these date parameters, listed as "START TIME", "COST", and "USAGE".
In the cells under this sub table, I want to place formulas that do this:
* Given the date interval cells, search the main table's START TIME (categories = hour blocks) and sum the values for COST and USAGE.
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.
i have a question for formula...
I have to made a report that contains different items. the report is based on another sheet which contains different items that had issued received , put on production on many dates. I have to made formula that allow range of dates of one particular items to be appear says in production line.
Please help me in this regards.
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.
I use the following file as the source of my SUM(IF(COUNTIFS formula, a revision of one used in your blog for "Sum if between two dates and another criteria":
Date Injury Location Gender Age Group Incident Type Plant Report Type Shift Department Incident Cost Days Lost
1-Jan-20 Multiple Male 25-34 Burn Iowa Near Miss Afternoon Painting $- 0.00
3-Jan-20 N/A Male 35-49 Vehicle Alabama Lost Time Day Fabrication $3,367 0.50
3-Jan-20 Eye Male 18-24 Cut Georgia Near Miss Day Administration $- 0.00
My formula [=SUM(IF(Data!$A$2:$A$515=DATE(2020,1,1)),1/COUNTIFS(Data!$F$2:$F$515,$A3,Data!$J$2:$J$515,">=100",Data!$A$2:$A$515,"="&DATE(2020,1,1))),0)]
It returns an unexpected value of 18 to display the number of incident each month at each plant which reported plant injury costs which exceed $100. For Iowa, the expected return value is 9 for the month of January.
Very helpful. Thanks a lot.
I am trying to develop an Excel formula to obtain a count.
If an ID number appears multiple times in one column and the result “received” appears one or more times in a different column, what is the formula to count only 1 instance of the occurrence of the ID and “received”?
For example:
Column Column
F J
5596326 attempted
5596326 delivered
5596326 received
5596326 received
5596326 sent
5596326 Wrong Number
The ID number appears 6 times in column F, and “received” appears twicein column J. With a correct formula, the result should be 1.
What is the formula?
Thank you for any assistance.
Hi,
Not sure if I understood your ask here, but try below formula in H1.
=COUNTIFS($F$1:$F$6,F1,$G$1:$G$6,G1)
5596326 attempted 1
5596326 delivered 1
5596326 received 2
5596326 received 1
5596326 sent 1
5596326 Wrong Number 1