none
sum formula for data between two dates RRS feed

  • 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 1-8-14 to 15-8-14 and from 16-8-14 to 31-8-14. How to do this please help.

    regards

     

    Friday, September 5, 2014 7:54 AM

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...

    http://1drv.ms/1weaISh

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Monday, September 8, 2014 4:39 PM

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 xl-central.com - "For Your Microsoft Excel Solutions"

    Saturday, September 6, 2014 7:39 PM
  • 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 1-2, 3-6, 7-13 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
    Sunday, September 7, 2014 9:41 AM
  • It works this way "=(SUM(IF(DATA!$D:$D="-",0,IF(DATA!$B:$B>=$A$1,IF(DATA!$B:$B<=$A$2,DATA!I:I))),0))" but calculation is extremely slow. Any other way please. 

    Sunday, September 7, 2014 10:10 AM
  • As I've already mentioned, instead of using whole column references, use dynamic named ranges...


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Sunday, September 7, 2014 6:42 PM
  • Hi Domenic Tamburino,

    Thank you,

    I know that but it is a very big sheet with nearly 500 columns and user form is used for data entry. please help with other formula like sumproduct or else one.

    regards

    Sunday, September 7, 2014 7:18 PM
  • 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 xl-central.com - "For Your Microsoft Excel Solutions"

    Sunday, September 7, 2014 7:56 PM
  • 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
    Sunday, September 7, 2014 8:10 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 xl-central.com - "For Your Microsoft Excel Solutions"



    Sunday, September 7, 2014 10:08 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
    Monday, September 8, 2014 1:06 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 xl-central.com - "For Your Microsoft Excel Solutions"


    Monday, September 8, 2014 3:12 AM
  • No it does not please help

    thank you

    Monday, September 8, 2014 6:15 AM
  • Hi

    http://dropcanvas.com/gga67

    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
    Monday, September 8, 2014 12:44 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...

    http://1drv.ms/1weaISh

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Monday, September 8, 2014 4:39 PM
  • 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
    Wednesday, September 10, 2014 9:18 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

    Wednesday, September 10, 2014 9:27 AM
  • 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 xl-central.com - "For Your Microsoft Excel Solutions"

    Wednesday, September 10, 2014 3:48 PM