Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Get Sequence of Date then sum in sum sheet

    Hello Gurus,

    I am really stuck in my senario please help me out.

    I have one calculation sheet and other is Sum sheet. In the calculation sheet dates are random suppose in the month of november 13 the date sequence is 2-Nov-13 and 3-Nov-13 I need to get those dates in Sum sheet like start date is 2-Nov-13 and End Date is 3-Nov-13 With the sum of values of those dates. Similarly if dates comes like 11-Nov-13 , 12-Nov-13 , 13-Nov-13 in calculation sheet, I need the start date which will be 11-Nov-13 and End date will be 13-Nov-13 in sum Sheet with sum of those 3 days.

    This is quite big sheet i only attached the sample data.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    in D4:
    =SUMIFS(Caculation!$G$4:$G40,Caculation!$B4:$B40," >="&B4,Caculation!$B4:$B40,"<="&C4)

    Change the range as appropriate and copy down the column.


    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Capture.PNG

    Hello sdckapr,

    The formula will get me the sum from calculation sheet but what about date range. For date range i need if dates are going in the sequence suppose 2-nov-13 then 3-nov-13 after that there is a date break 11-nov-13 to 13-nov and then 19-nov-13 to 21-nov-13.

    I need the sum of values ,start date ,end date based on date break?
    sorry if i cannot clear

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't understand. The sum uses the date range given and only calculates the dates within that range. The range can be 1 day, 2 days or any number of days.

    If you want to extract the dates as well, it is more complicated since you have irregular counting and even blanks in some of the sequences. This array (confirm with ctrl-shift-enter) seems to work in B4 for the sample:
    =MIN(INDIRECT("Caculation!"&ADDRESS(MATCH(A4,Cacul ation!A:A,0),2)&":"&ADDRESS(MATCH(A4+1,Caculation! A:A,0)-1,2)))

    and in C4 (again confirm with ctrl-shift-enter:
    =MAX(INDIRECT("Caculation!"&ADDRESS(MATCH(A4,Cacul ation!A:A,0),2)&":"&ADDRESS(MATCH(A4+1,Caculation! A:A,0)-1,2)))

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Samplescreenshot.PNG

    Hello sdckapr,

    Sorry i think i unable to clear my forum help.

    Actually the Sum sheet will be created automatically there is no match case.
    I have given the coloring to cells to make clear myself.
    If the dates are going in the sequence e.g 2-Nov-13 onward in the attached screen shot there is a break of sequence after 3-Nov-13, so those date range plus sum of those dates to be show in the Sum Sheet next in the screenshot 11-Nov-13 start date and end date will be 13-Nov-13. I need the date which is 11-Nov-13 just after date break of sequence and where the date break comes which is 13-Nov-13 and get the sum of those days.

    Thanks

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Have you tried the formula I have posted? That is what I believe the formulas I have posted do...

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Thanks Sdckpar,
    Exactly your post # 4 works for me . I am unable to fix the below formula to get the sum.

    =SUMIFS(Caculation!$G$4:$G40,Caculation!$B4:$B40," >="&B4,Caculation!$B4:$B40,"<="&C4)

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    what is the problem exactly with the formula? In the sample it gave the results you listed...

    Steve

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    farrukh (2013-12-25)

  10. #9
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Ahh sorry in the sampler i copy paste the values.Please see the attached spreadsheet what i am doing wrong.
    Attached Files Attached Files

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Try this in D4
    =SUMIFS(Caculation!$G$4:$G$40,Caculation!$B$4:$B$4 0,">="&B4,Caculation!$B$4:$B$40,"<="&C4)

    Copy it down the column.

    Steve
    PS there is not space before the greater than sign (>) and the ranges should be "locked"

  12. #11
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sdckpar many thanks worked perfectly...
    Thanks for help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •