Answered by:
sum formula for data between two dates
Question

Hi,
I have formula for calculation of data Month wise. e.g. =SUM(IF(MONTH(DATA!$B:$B)=$A8,DATA!I:I,0)). And it is working nice. But I need it to calculate between two dates. Suppose I data from date 1814 to 15814 and from 16814 to 31814. How to do this please help.
regards
Answers

Sorry, but I don't have the time to look your sample workbooks. But here's a sample file I had prepared when I first offered you a solution...
Hope this helps!
Domenic Tamburino Microsoft MVP  Excel xlcentral.com  "For Your Microsoft Excel Solutions"
 Marked as answer by George HuaModerator Monday, September 15, 2014 8:30 AM
All replies

Try the following formulas, which need to be confirmed with CONTROL+SHIFT+ENTER...
=SUM(IF(DATA!$B:$B>=DATE(2014,8,1),IF(DATA!$B:$B<=DATE(2014,8,15),DATA!I:I)))
or
=SUM(IF(DATA!$B:$B>=A2,IF(DATA!$B:$B<=B2,DATA!I:I)))
...where A2 contains the start date, and B2 contains the end date. However, I would suggest avoiding whole column references and use references to fixed ranges instead. If the ranges will increase (or decrease) over time, try using dynamic named ranges. The size of these ranges will automatically adjust as data is added (or removed).
Hope this helps!
Domenic Tamburino Microsoft MVP  Excel xlcentral.com  "For Your Microsoft Excel Solutions"

Hi Domenic Tamburino,
Thank you,
I am sorry, I thought I will modify this formula as needed but I could not. I need to add that if DATA!D:D="" and not like 12, 36, 713 etc. then it should not included to calculate. I modified it like this
"=IF(DATA!D:D="",0,SUM(IF(DATA!$B:$B>=A2,IF(DATA!$B:$B<=B2,DATA!I:I)))". It is not working for me. Please help. This formula made excel response very slow !! Any other better way ??
regards
 Edited by dockhem2 Sunday, September 7, 2014 9:48 AM


As I've already mentioned, instead of using whole column references, use dynamic named ranges...
Domenic Tamburino Microsoft MVP  Excel xlcentral.com  "For Your Microsoft Excel Solutions"


First, while SUMPRODUCT can be used, it's still an array formula. Although, yes, there's no need to confirm the formula with CONTROL+SHIFT+ENTER. Also, it probably wouldn't be as efficient as SUM(IF(...)).
In your formula, you reference three columns  Column B, Column D, and Column I. So, here, for example, you would create three dynamic named ranges. Now you say that your sheet contains about 500 columns. So are you saying that you will be referencing other columns as well? If so, roughly how many more columns? And, if so, are the formulas similar? In this case, we can create one dynamic named range, which will define all of the data. Then, we can use the column label/header to reference the desired column for our formulas.
Domenic Tamburino Microsoft MVP  Excel xlcentral.com  "For Your Microsoft Excel Solutions"

The formula will be similar for each column. Only last column goes I:I to J:J , K:K, L:L and so on and formula in same column as this I:I to J:J , K:K, L:L and so on, similar in source sheet i.e. DATA and sheet where sum is done. But these columns are many roughly 500. That I will adjust later. If we make dynamic range, will it disturb userform data entry to sheet DATA ?
REGARDS
 Edited by dockhem2 Sunday, September 7, 2014 8:13 PM

Assumptions:
1) The "DATA" sheet contains the data.
2) The data starts at Column A.
3) The first row contains the column labels/headers.
4) Each column within the data contains a label.
5) The label for Column B is "Date".
6) The label for Column D is "HeaderD" (change accordingly).
7) The labels for Column I, J, K, L, etc. are "Week1", "Week2", "Week3", "Week4", etc. (change accordingly).
Defined Name:
Ribbon > Formulas > Defined Names > Define Name
Name: MyArray
Refers to: =OFFSET(DATA!$A$1,,,COUNTA(DATA!$B:$B),COUNTA(DATA!$1:$1))
Click OK
Formula:
On your results sheet, assuming that A2 contains the start date, B2 contains the end date, and C1, D1, E1, F1, etc., contains "Week1", "Week2", "Week3", "Week4", etc., try...
C2, confirmed with CONTROL+SHIFT+ENTER, copied across:
=SUM(IF(INDEX(MyData,0,MATCH("Date",INDEX(MyData,1,0),0))>=$A2,IF(INDEX(MyData,0,MATCH("Date",INDEX(MyData,1,0),0))<=$B2,IF(INDEX(MyData,0,MATCH("HeaderD",INDEX(MyData,1,0),0))="",INDEX(MyData,0,MATCH(C$1,INDEX(MyData,1,0),0))))))
Make sure that you change "HeaderD" to the correct header for Column D.
Hope this helps!
Domenic Tamburino Microsoft MVP  Excel xlcentral.com  "For Your Microsoft Excel Solutions"
 Edited by Domenic Tamburino Sunday, September 7, 2014 10:11 PM

Hi Domenic Tamburino,
Thank you for your kind reply,
When I use same as above, it says, #NAME?, When I change MyData in formula with MyArray, it shows all results 0,0,0. When I change $A2 and $B2 according to my cells, it says, #N/A. Please forgive my ignorance. I do not understand index formula. please help here ,
regards
This is fresh file http://1drv.ms/1quAL7y
similar file I used, http://1drv.ms/1wdBGtp
 Edited by dockhem2 Monday, September 8, 2014 1:30 AM

Sorry, my mistake. The name used to define the dynamic named range should be MyData, not MyArray...
Name: MyData
Does this help?
Domenic Tamburino Microsoft MVP  Excel xlcentral.com  "For Your Microsoft Excel Solutions"
 Edited by Domenic Tamburino Monday, September 8, 2014 3:14 AM


Hi
Go see the attached file and check the last sheet with one calculation with some explanation.
If that don't work for you, you will need to give more explanation exactly what you want.
To speed up your calculation, you shouldn't use full range. Anyway have a look at the file and post back
P.S. Don't forget, its an "ARRAY" formula.
Every time you edit the cell, you need to press Ctrl+Shift+Enter not just Enter.
Edit at 1:00 PM
I saw your other form with the dates in the first and second row. So you could use the cell reference and also a none array formula in both case.
=SUMPRODUCT((DATA!B4:B60>=A1)*(DATA!B4:B60<=A2)*(DATA!J4:J60))
But I read many times in these forums that Sumproduct was notorious for being slow.
I don't know if the array formula is faster and would make a difference.
Cimjet
 Edited by Cimjet Monday, September 8, 2014 5:06 PM

Sorry, but I don't have the time to look your sample workbooks. But here's a sample file I had prepared when I first offered you a solution...
Hope this helps!
Domenic Tamburino Microsoft MVP  Excel xlcentral.com  "For Your Microsoft Excel Solutions"
 Marked as answer by George HuaModerator Monday, September 15, 2014 8:30 AM

Hi Cimjet,
Thank you ,
It is working but the same problem slowness. I used this way
=SUM(IF(DATA!$D$4:$D$25000="",0,IF(DATA!$B$4:$B$25000>=DATE(2014,4,1),IF(DATA!$B$4:$B$25000<=DATE(2014,4,15),DATA!H$4:H$25000))))
I used long column because The data may reach there in a year. Is there any correction needed in formula.
I also used sum product formula, this way,
=IFERROR(SUM(SUMPRODUCT((DATA!$B$4:$B$25000>=$C65402)*(DATA!$B$4:$B$25000<=$D65402)*(DATA!H$4:H$25000)))((SUMPRODUCT((DATA!$B$4:$B$25000>=$C65402)*(DATA!$B$4:$B$25000<=$D65402)*(DATA!$D$4:$D$25000="")*(DATA!H$4:H$25000)))),0)
Again working but slow.
Is it possible to change that monthly macro according to date ?
regards,
dockhem
 Edited by dockhem2 Wednesday, September 10, 2014 9:19 AM

Hi Domenic Tamburino ,
Thank you,
It is working. I changed formula. I needed, if B4:B has entry,  , then data should not be added. It goes like,
=SUM(IF(INDEX(MYDATA,0,MATCH("DATE",INDEX(MYDATA,1,0),0))>=$C65401,IF(INDEX(MYDATA,0,MATCH("DATE",INDEX(MYDATA,1,0),0))<=$D65401,IF(INDEX(MYDATA,0,MATCH("ANNUAL SERIAL",INDEX(MYDATA,1,0),0))="",0,INDEX(MYDATA,0,MATCH(H$65400,INDEX(MYDATA,1,0),0))))))
I found it working but on long run ? Is my modified formula correct or any correction please ?
It has good speed with dynamic range but how will be when data goes on increasing ?
Please tell me any further correction ?
regards,
dockhem

The formula looks fine. The range is dynamic. So the range will automatically increase/decrease as data is added/removed. So there will be no need to manually adjust the references.
I'm glad to hear that it has good speed.
Cheers!
Domenic Tamburino Microsoft MVP  Excel xlcentral.com  "For Your Microsoft Excel Solutions"