Sum Blocks of data. Function or VBA

[excelnovi]

Hi all,

I think il need VBA for this one. I would appreciate any help.

I have 100,000 rows of data in cell A1.

I want to sum 5 rows at a time and put the results in column B starting b1,b2,b3 etc

So the sum of a1 to a5 goes into b1, sum of a6 to a10 goes into b2 and so on.

Thanks for your help

 

[Aladin Akyurek]

Hi all,

I think il need VBA for this one. I would appreciate any help.

I have 100,000 rows of data in cell A1.

I want to sum 5 rows at a time and put the results in column B starting b1,b2,b3 etc

So the sum of a1 to a5 goes into b1, sum of a6 to a10 goes into b2 and so on.

Thanks for your help

In B1 ente and copy down:

=SUM(INDEX($A$1:$A$100000,(ROWS(B$1:B1)-1)*5+1):INDEX($A$1:$A$100000,(ROWS(B$1:B1))*5))

 

[Jonmo1]

Welcome to the board...

Try

=SUM(OFFSET(A$1,(ROWS(B$1:B1)-1)*5,0,5,1))

Hope that helps.

 

[excelnovi]

Thanks very much Jonmo works perfect!!

I am new to vba and have been trying to write this simple loop for a while now would rally like if someone could show me how to do it with VBA.