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

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?

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"
skrev i en meddelelse
...
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?

"ExcelQuestionStephen"
wrote in message ...
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

Thanks, that was very helpful

"Leo Heuser" wrote:

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"
skrev i en meddelelse
...
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?

"excelquestionstephen"
skrev i en meddelelse
...
Thanks, that was very helpful

You're welcome and thanks for the feedbacbk :-)

LeoH