How do I sum a formula and paste it in 7 cell increments?

[Guest]

I have two worksheets I'm working with. One of them is a running total of
cash for each day of the month. The second worksheet shows only the weekly
totals which come from the sum of 7 days from the first worksheet. I want to
know if its possible to copy the first week sum(A1:A7) and use some type of
formula to then copy the formula down in 7 day increments and not have to do
this manually. So the second entry would be sum(A8:A15), then sum(A16:A23),
etc.
Is this possible?

 

[Leo Heuser]

Stephen

One way:

In D2:
=SUM(OFFSET($A$1,(ROW()-ROW($D$2))*7,,7))

Copy down with the fill handle (the little square in the
lower right corner of the cell)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"ExcelQuestionStephen" <[email protected]>
skrev i en meddelelse
news:[email protected]...

 

[Fredrik Wahlgren]

I have two worksheets I'm working with. One of them is a running total of
cash for each day of the month. The second worksheet shows only the weekly
totals which come from the sum of 7 days from the first worksheet. I want to
know if its possible to copy the first week sum(A1:A7) and use some type of
formula to then copy the formula down in 7 day increments and not have to do
this manually. So the second entry would be sum(A8:A15), then sum(A16:A23),
etc.
Is this possible?

You can use a macro to create these formulas. The code below will enter
weekly totals in column B

Public Sub FillWeeklySum()
Dim i As Long

For i = 0 To 12
If 0 = i Then
Range("B" & CStr(i + 1)).FormulaR1C1 = "=SUM(A1:A7)"
Else
Range("B" & CStr(i + 1)).FormulaR1C1 = "=SUM(A" & CStr(i * 8) & ":A"
& CStr((i * 8) + 7) & ")"
End If
Next i
End Sub

/Fredrik

 

[Guest]

Thanks, that was very helpful

Stephen

One way:

In D2:
=SUM(OFFSET($A$1,(ROW()-ROW($D$2))*7,,7))

Copy down with the fill handle (the little square in the
lower right corner of the cell)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"ExcelQuestionStephen" <[email protected]>
skrev i en meddelelse

 

[Leo Heuser]

"excelquestionstephen" <[email protected]>
skrev i en meddelelse

Thanks, that was very helpful

You're welcome and thanks for the feedbacbk

LeoH