Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Trying to work out a formula to sum 7 days of data

    Hi All,
    I have a series of data that is tacking hours per day spent on various tasks.
    This data then needs to be summerised to a weekly format.
    Yes a simple SUM formula would normally be the answer but in this instance, it would involve a lot of effort to create all the formula across the columns for the 'year'.
    i.e. the summary area is in Weeks and the source is in Days, so for each summary cell I need to sum 7 days starting from a selected date.
    In the process of copying the weekly formula across the formula moves the source cell by one, as the destination cell is only moved by one... herein lies the problem, if the destination cell is moved 7 columns to the right, then the source destination will now have the next 'weeks' summary.
    I created a VBA routine that does work, once, then plays dumb and does not update again unless you 'edit' the formula.
    Hopefully I have made some sense to describing this problem.

    Rows 3 thru 8 are the input rows, where hours spent on tasks are entered for each day.
    I have created rows 11 thru 16 using my 'User Defined Function'
    Rows 19 thru 24 have the 'Sum' formula but as you will see, column C values are correct, but dragging the formula to the right makes the next two columns incorrect.

    Can anyone come up with a solution?

    TIA Brian.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Brian,

    I think this may be what you want.
    =SUMIFS($5:$5,$2:$2,">="&C$10,$2:$2,"<="&C$11)

    Place in C14 & fill across the row.

    BrianSumifs.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts
    hi RG,
    Awesome! Thanks for your solution.
    Works a treat.
    Took a bit of reading in the help file to totally understand your solution, but all good now.
    Cheers
    Brian.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi BRoby

    ..you probably worked out that if you remove the dollar signs from row5 in RG's formula, i.e. use
    =SUMIFS(5:5,$2:$2,">="&C$10,$2:$2,"<="&C$11)
    ..then you could copy that formula to all the cells i.e. from C12 down to C17, and then across the rows.

    You could also have used a SUM(OFFSET(...)) formula, but RG's does the job nicely. In either case, you can dispense with your custom Function, so don't need to use VBA in this case.

    zeddy

Posting Permissions

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