How to calculate fixed rows in Excel ( Sum of every 7 rows for i..

[Guest]

hi,
in a personal expense spreadsheet, one sheet is detailed spending
everyday, another linked sheets are Weekly and Monthly, in which i want know
how to drag down the formula vertically with 7 rows at a time till the end of
52 weeks(a year), because Excel itself automatically move one row
down....say, i select A1 to A6 and sum it but when draging down, it is from
A2 to A7, but i wanted A7 to A13, then A14 to A20.......it would be nice if i
can set this up and drag all the way down.......

anybody knows how? tks a lot!

 

[Frank Kabel]

Hi
try the following formula:
=OFFSET('other_sheet'!$A$1,(ROW(1:1)-1)*7,0)
and copy down

 

[Aladin Akyurek]

=SUM(OFFSET(A$1,(ROW()-ROW($A$1))*7,0,7,1))

A1 to A6 is 6 cells, while A7 to A13 counts 7 cells. Replace 7 with 6 if 6
is what you intended.

 

[Max]

Assume the daily expenses are in Sheet1, A1 downwards
(Day1's expenses in A1, Day2's in A2, and so on)

In your sheet: Weekly
------------------------------
Put in A1:

=SUM(INDIRECT("Sheet1!A"&ROW(A1)*7-6&":A"&ROW(A1)*7))

The formula is functionally equivalent to: =SUM(Sheet1!A1:A7)
which returns week 1's expenses from Sheet1

Copy A1 down till A52

When you copy down, the formula will increment nicely
and return the equivalent of:

In A2: =SUM(Sheet1!A8:A14)
In A3: =SUM(Sheet1!A15:A21)
and so on ..

So the above will return the weekly expenses
for all 52 weeks from Sheet1 in A1 to A52

 

[Max]

Clarification: Had assumed there were typos in your post ..

....say, i select A1 to A6 and sum it but when draging down, it is
from A2 to A7, but i wanted A7 to A13, then A14 to A20

and that you actually wanted the summations of 7 days expenses
at a stretch when the formula in the start cell is copied down, viz.:

A1:A7
A8:A14
A15:A21
etc

 

[Guest]

tks a lot! i will try;-)

have a nice day!

tony

“Frank Kabelâ€ç¼–写：

Hi
try the following formula:
=OFFSET('other_sheet'!$A$1,(ROW(1:1)-1)*7,0)
and copy down

 

[Guest]

tks a lot! i will try out

have a nice day!

tony

“Maxâ€ç¼–写：

Assume the daily expenses are in Sheet1, A1 downwards
(Day1's expenses in A1, Day2's in A2, and so on)

In your sheet: Weekly
------------------------------
Put in A1:

=SUM(INDIRECT("Sheet1!A"&ROW(A1)*7-6&":A"&ROW(A1)*7))

The formula is functionally equivalent to: =SUM(Sheet1!A1:A7)
which returns week 1's expenses from Sheet1

Copy A1 down till A52

When you copy down, the formula will increment nicely
and return the equivalent of:

In A2: =SUM(Sheet1!A8:A14)
In A3: =SUM(Sheet1!A15:A21)
and so on ..

So the above will return the weekly expenses
for all 52 weeks from Sheet1 in A1 to A52

 

[Max]

You're welcome, Tony!
Thanks for the feedback