Summing every 7 cells consecutively

[ihorganization]

If I have number in every cell A1:A14 and would like to have cell B1
show the sum of the first seven rows (A1:A7), and B2 show the sum of
the next seven (A8:A14), how do I do this automatically? I.E. I have
thousands of rows of data, and would like a formula that I can drag
down, which will sum every 7 rows.

Thanks!

 

[eagleyzw]

Cell B1:
{=SUM((ROW($A$1:$A$65535)<=ROW()*7)*(ROW($A$1:$A$65535)>=ROW()*7-6)*$A$1:$A$65535)}

then you can can drag down

 

[Jim Cone]

=SUM(OFFSET($A$1,(ROW()-1)*7,0,7,1))
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
If I have number in every cell A1:A14 and would like to have cell B1
show the sum of the first seven rows (A1:A7), and B2 show the sum of
the next seven (A8:A14), how do I do this automatically? I.E. I have
thousands of rows of data, and would like a formula that I can drag
down, which will sum every 7 rows.
Thanks!

 

[ihorganization]

=SUM(OFFSET($A$1,(ROW()-1)*7,0,7,1))
--
Jim Cone
Portland, Oregon  USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

<[email protected]>
wrote in message
If I have number in every cell A1:A14 and would like to have cell B1
show the sum of the first seven rows (A1:A7), and B2 show the sum of
the next seven (A8:A14), how do I do this automatically?  I.E.  I have
thousands of rows of data, and would like a formula that I can drag
down, which will sum every 7 rows.
Thanks!

Thanks for your help!

 

[Gord Dibben]

=SUM(INDEX(A:A,ROW(A1)*7-6):INDEX(A:A,ROW(A1)*7))


Gord Dibben MS Excel MVP