filling weekly totals in new column

[marklmiddleton]

This seems like this would be a common question to me, but I've searche
the forums and can't find it, so I'm sorry if it's already on here.

Here's what I would like to do:

I have one column of daily totals. I would like to have weekly total
in a new column on another sheet without spaces between them, and
want to be able to fill the weekly totals downwards, and have eac
weekly total reflect a new set of 7 cells. So, say the first formula i
the weekly total column is =SUM(B2:B8), and the second formula i
=SUM(B9:B15). When I select these two cells, I want the next one to b
=SUM(B16:B22) or the equivalent, and not =SUM(B10:B16), like it is whe
I drag it down.

Is this clear?

Thank you for your help.

Mar

 

[Biff]

Hi!

Here's one way:

=SUM(OFFSET(B$2,(ROW(A1)-1)*7,0,7,1))

Biff

 

[Myrna Larson]

I'll assume you have headers on row 1 on your weekly totals sheet, so the
first sum will go in row 2

=SUM(OFFSET(Sheet1!$B$2,(ROW()-2)*7,0,7,1))

You will have to modify the formula if you start it in a row other than 2. The
"-2" in the formula comes from the row in which the first formula is located.
If you put it in row 1, it sould be -1; if you put it in row 10, it would be
-10. Here's the logic: in the first formula (ROW()-x)*7 must evaluate to 0; in
the 2nd formula, to 7, in the 3rd to 14, then to 21, etc.