hi,

i need to sum data from every 100 rows from top to bottom of total 50,000
rows. how to do it?
my data have two columns:
id score
1 45
2 60
3 85
.. .
.. .
.. .
100 96
.. .

so, the sum of 1st 100 rows =45+60+85+...+96
then next 100 rows, ... until to 50,000 rows.

thanks for your help.

pemt

Use this to test and then change to step 10, resize 9

Sub sumeverfewrows()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row Step 4
MsgBox Application.Sum(Cells(i, "b"), Cells(i, "b").Resize(3))
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pemt" wrote in message
...
hi,

i need to sum data from every 100 rows from top to bottom of total 50,000
rows. how to do it?
my data have two columns:
id score
1 45
2 60
3 85
. .
. .
. .
100 96
. .

so, the sum of 1st 100 rows =45+60+85+...+96
then next 100 rows, ... until to 50,000 rows.

thanks for your help.

pemt

Say values in Column B, starting in B1 down:

Enter this formula *anywhere* (except Column B), and copy down as needed:
=SUM(INDEX(B:B,100*ROWS($1:1)-99):INDEX(B:B,ROWS($1:1)*100))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"pemt" wrote in message
...
hi,

i need to sum data from every 100 rows from top to bottom of total 50,000
rows. how to do it?
my data have two columns:
id score
1 45
2 60
3 85
. .
. .
. .
100 96
. .

so, the sum of 1st 100 rows =45+60+85+...+96
then next 100 rows, ... until to 50,000 rows.

thanks for your help.

pemt

thanks a lot!

"Don Guillett" wrote:

Use this to test and then change to step 10, resize 9

Sub sumeverfewrows()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row Step 4
MsgBox Application.Sum(Cells(i, "b"), Cells(i, "b").Resize(3))
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pemt" wrote in message
...
hi,

i need to sum data from every 100 rows from top to bottom of total 50,000
rows. how to do it?
my data have two columns:
id score
1 45
2 60
3 85
. .
. .
. .
100 96
. .

so, the sum of 1st 100 rows =45+60+85+...+96
then next 100 rows, ... until to 50,000 rows.

thanks for your help.

pemt

thanks a lot!

"Ragdyer" wrote:

Say values in Column B, starting in B1 down:

Enter this formula *anywhere* (except Column B), and copy down as needed:
=SUM(INDEX(B:B,100*ROWS($1:1)-99):INDEX(B:B,ROWS($1:1)*100))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"pemt" wrote in message
...
hi,

i need to sum data from every 100 rows from top to bottom of total 50,000
rows. how to do it?
my data have two columns:
id score
1 45
2 60
3 85
. .
. .
. .
100 96
. .

so, the sum of 1st 100 rows =45+60+85+...+96
then next 100 rows, ... until to 50,000 rows.

thanks for your help.

pemt

You're welcome, and appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"pemt" wrote in message
...
thanks a lot!

"Ragdyer" wrote:

Say values in Column B, starting in B1 down:

Enter this formula *anywhere* (except Column B), and copy down as needed:
=SUM(INDEX(B:B,100*ROWS($1:1)-99):INDEX(B:B,ROWS($1:1)*100))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"pemt" wrote in message
...
hi,

i need to sum data from every 100 rows from top to bottom of total
50,000
rows. how to do it?
my data have two columns:
id score
1 45
2 60
3 85
. .
. .
. .
100 96
. .

so, the sum of 1st 100 rows =45+60+85+...+96
then next 100 rows, ... until to 50,000 rows.

thanks for your help.

pemt

Glad to help. You didn't say what you used.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pemt" wrote in message
...
thanks a lot!

"Don Guillett" wrote:

Use this to test and then change to step 10, resize 9

Sub sumeverfewrows()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row Step 4
MsgBox Application.Sum(Cells(i, "b"), Cells(i, "b").Resize(3))
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pemt" wrote in message
...
hi,

i need to sum data from every 100 rows from top to bottom of total
50,000
rows. how to do it?
my data have two columns:
id score
1 45
2 60
3 85
. .
. .
. .
100 96
. .

so, the sum of 1st 100 rows =45+60+85+...+96
then next 100 rows, ... until to 50,000 rows.

thanks for your help.

pemt