# Thread: Trying to work out a formula to sum 7 days of data

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

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

3. hi RG,
Works a treat.
Took a bit of reading in the help file to totally understand your solution, but all good now.
Cheers
Brian.

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