Results 1 to 4 of 4

20150903, 08:30 #1
 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.

20150903, 08:56 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,864
 Thanks
 416
 Thanked 1,576 Times in 1,427 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
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150903, 23:34 #3
 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.

20150904, 04:54 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,344
 Thanks
 162
 Thanked 621 Times in 590 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