Copying and Pasting a Forumula with a Different Auto Increment Value

[althius1]

I've been searching Google high and low, and I'm guessing I'm using the wrong keywords, so I need assistance please.

I have a spreadsheet with dates and times running down two columns. I want to add the total hours in a week so I do something like =SUM(F5:F11).

Then I need the hours for the next week. If I copy and paste I get =SUM(F6:F12), but really I want =SUM(F12:F18).

Is there a way to accomplish this without manually setting the two values each week.

Thank you in advance. I have a feeling this is either very simple, or very hard...

[ZENITH]

It would be easier to give you suggestions if you uploaded a mockup with the formula's in the exact spots as your workbook.

At the moment It sounds like you are copying this data one cell below where the other formula is, the problem with this is that it will only increment the formula once per every row you move it down.

[althius1]

Yes, that is exactly what is happening, you understand the situation perfectly. How do I get it to 'auto-increment' by 7s rather than 1s.

[martindwilson]

=SUM(OFFSET($F$5,(ROWS($A$1:$A1)*7-7),0,7)) will sum the first 7
copy/paste below it will sum the next 7 and so on
or you could just drag it down

[althius1]

Brilliant. Thanks so much.

[ZENITH]

If you were satisfied with the answer provided please mark your thread solved, directions below:

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save