excel formula

kal · Sep 12, 2002

I am sure this is a simple formula, but i must have some kind of mind block. I am trying to sum every 60 cells consecutively in a clumn of 1000's of data cells. Any ideas?

thanks in advance

Dave Patton · Sep 12, 2002

On 2002-09-12 18:38, kal wrote:
I am sure this is a simple formula, but i must have some kind of mind block. I am trying to sum every 60 cells consecutively in a clumn of 1000's of data cells. Any ideas?

thanks in advance

If you can start on an adjacent column in row 1 consider

=SUM(OFFSET($B$1,60*ROW()-1,0,-60,1))

or insert say in column K information like
the following (to create via formula see bottom):

B1:B60
B61:B120
B121:B180

then use formula

=SUM(EVAL(K1))

Eval is part of Morefunc add-in

Download the morefunc add-in from

http://longre.free.fr/english/index.html

and install/activate the add-in via Tools|Add-Ins.

The offset can be messed up if someone inserts a row at say A2.

To build the information for Eval,
consider

="B"&(ROW()-1)*60+1&":"&"B"&ROW()*60

If you want, You can then copy the cells with the above and paste special values to leave just the text.
This message was edited by Dave Patton on 2002-09-12 21:17

eliW · Sep 13, 2002

On 2002-09-12 18:38, kal wrote:
I am sure this is a simple formula, but i must have some kind of mind block. I am trying to sum every 60 cells consecutively in a clumn of 1000's of data cells. Any ideas?

thanks in advance

Hi kal,
Look at my example below:
(1) The data is in column A
(2) In C1 enter "a1" and in D1 "a60"
(3) In C2 enter "a61" and in D2 "a120"
(4) select C1:D2 (in yellow) and drag down until you'll get your last row of data. This will copy cell references in intervals of 60.
(5) Now enter the formula to sum as shown in my example.
Complicated but simple.

*My example shows 5 cells intervals.

Eli

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=6>Microsoft Excel - Book1_______________Running: xl97 : OS = Windows (32-bit) 4.10</TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>E1</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=3 BGCOLOR=White>=SUM(INDIRECT(C1):INDIRECT(D1))</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF00 ALIGN=Center VALIGN=BOTTOM >a1</TD><TD BGCOLOR=#FFFF00 ALIGN=Center VALIGN=BOTTOM >a5</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert("=SUM(INDIRECT(C1):INDIRECT(D1))")>15</A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF00 ALIGN=Center VALIGN=BOTTOM >a6</TD><TD BGCOLOR=#FFFF00 ALIGN=Center VALIGN=BOTTOM >a10</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert("=SUM(INDIRECT(C2):INDIRECT(D2))")>40</A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a11</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a15</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert("=SUM(INDIRECT(C3):INDIRECT(D3))")>65</A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a16</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a20</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert("=SUM(INDIRECT(C4):INDIRECT(D4))")>90</A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a21</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a25</TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >6</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a26</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a30</TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a31</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a35</TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >8</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a36</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a40</TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>9</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >9</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a41</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >a45</TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>10</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >10</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>11</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >11</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>12</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >12</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>13</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >13</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>14</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >14</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>15</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >15</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>16</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >16</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>17</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >17</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>18</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >18</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>19</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >19</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>20</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM >20</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>21</TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM >etc</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=6>Sheet1</TD></TR></TABLE>
To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.23]
If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you.
This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo
</CENTER>

sen_edp · Sep 13, 2002

hello

for a range a1:a1000 try this array formula

=SUMPRODUCT((MOD(ROW(A1:A1000)-ROW(A1);60)=0)*(A1:A1000))

press ctrl-shift-enter when you enter it
hth

_________________
Best Regards,AndreaSusing xl2000
This message was edited by sen_edp on 2002-09-13 00:32

Joel Horowitz · Sep 13, 2002

An easy way is to add a column with row numbers: B1=1, B2=B1+1, etc... then add field names ('amount', 'n°') above your list, create a pivottable. Put the 'n°' field as a row field, and the 'amount' as a data field. Right click on the 'n° field' in the pivottable and choose 'group and outline'/'group', and then group by increments of 60

excel formula

kal

New Member

Excel Facts

Dave Patton

Well-known Member

eliW

Well-known Member

sen_edp

Well-known Member

Joel Horowitz

New Member

Similar threads

Forum statistics

Share this page

excel formula

kal

New Member

Excel Facts

Dave Patton

Well-known Member

eliW

Well-known Member

sen_edp

Well-known Member

Joel Horowitz

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock