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

  • Thread starter ExcelQuestionStephen
  • Start date
E

ExcelQuestionStephen

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?
 
L

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]...
 
F

Fredrik Wahlgren

ExcelQuestionStephen said:
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?
Click to expand...

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
 
E

excelquestionstephen

Thanks, that was very helpful

Leo Heuser said:
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
Click to expand...
 
Top