Sum rows in groups
 

I have a column with 10,000 rows of numbers. I need to sum a1:a5, a6:a10,
a11:a15, and so on in groups of five, for all 10,000 rows. How can I do
this? Thanks much.

=SUM(OFFSET(A1,5*ROW(A1)-1,0,5,1))

Put that in the first cell where you want the sums to start and copy it down
the column.

"sandy" wrote:

I have a column with 10,000 rows of numbers. I need to sum a1:a5, a6:a10,
a11:a15, and so on in groups of five, for all 10,000 rows. How can I do
this? Thanks much.

Can't get it Duke. The first sum is incorrect and the next formula down
starts with the next number copied down to (a2, a3, a4). What am I doing
wrong? Thanks for your help. Sandy

"Duke Carey" wrote:

=SUM(OFFSET(A1,5*ROW(A1)-1,0,5,1))

Put that in the first cell where you want the sums to start and copy it down
the column.

"sandy" wrote:

I have a column with 10,000 rows of numbers. I need to sum a1:a5, a6:a10,
a11:a15, and so on in groups of five, for all 10,000 rows. How can I do
this? Thanks much.

In article ,
"sandy" wrote:

I have a column with 10,000 rows of numbers. I need to sum a1:a5, a6:a10,
a11:a15, and so on in groups of five, for all 10,000 rows. How can I do
this? Thanks much.

Try...

B1, copied down:

=SUM(OFFSET($A$1,(ROW()-ROW($B$1)+1)*5-5,0,5))

Hope this helps!

Absolutely wonderful. Thank you for your help Domenic. Sandy

"Domenic" wrote:

In article ,
"sandy" wrote:

I have a column with 10,000 rows of numbers. I need to sum a1:a5, a6:a10,
a11:a15, and so on in groups of five, for all 10,000 rows. How can I do
this? Thanks much.

Try...

B1, copied down:

=SUM(OFFSET($A$1,(ROW()-ROW($B$1)+1)*5-5,0,5))

Hope this helps!

Sandy,

Try this instead

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"sandy" wrote in message
...
Can't get it Duke. The first sum is incorrect and the next formula down
starts with the next number copied down to (a2, a3, a4). What am I doing
wrong? Thanks for your help. Sandy

"Duke Carey" wrote:

=SUM(OFFSET(A1,5*ROW(A1)-1,0,5,1))

Put that in the first cell where you want the sums to start and copy it
down
the column.

"sandy" wrote:

I have a column with 10,000 rows of numbers. I need to sum a1:a5,
a6:a10,
a11:a15, and so on in groups of five, for all 10,000 rows. How can I
do
this? Thanks much.