excel formula

kal

New Member
Joined
Sep 11, 2002
Messages
1
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,468
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 2, 2002
Messages
1,931
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><FONT COLOR=WHITE>Microsoft Excel - Book1_______________Running: xl97 : OS = Windows (32-bit) 4.10</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6>(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)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 ><FONT FACE=Arial COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a1</FONT></TD><TD BGCOLOR=#FFFF00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a5</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert("=SUM(INDIRECT(C1):INDIRECT(D1))")><FONT FACE=Arial COLOR=#000000>15</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFF00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a6</FONT></TD><TD BGCOLOR=#FFFF00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a10</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert("=SUM(INDIRECT(C2):INDIRECT(D2))")><FONT FACE=Arial COLOR=#000000>40</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>3</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a11</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a15</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert("=SUM(INDIRECT(C3):INDIRECT(D3))")><FONT FACE=Arial COLOR=#000000>65</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a16</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a20</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert("=SUM(INDIRECT(C4):INDIRECT(D4))")><FONT FACE=Arial COLOR=#000000>90</FONT></A></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>5</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a21</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a25</FONT></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>6</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a26</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a30</FONT></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>7</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a31</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a35</FONT></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>8</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a36</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a40</FONT></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>9</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>9</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a41</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>a45</FONT></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>10</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>10</FONT></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 ><FONT FACE=Arial COLOR=#000000>11</FONT></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 ><FONT FACE=Arial COLOR=#000000>12</FONT></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 ><FONT FACE=Arial COLOR=#000000>13</FONT></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 ><FONT FACE=Arial COLOR=#000000>14</FONT></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 ><FONT FACE=Arial COLOR=#000000>15</FONT></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 ><FONT FACE=Arial COLOR=#000000>16</FONT></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 ><FONT FACE=Arial COLOR=#000000>17</FONT></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 ><FONT FACE=Arial COLOR=#000000>18</FONT></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 ><FONT FACE=Arial COLOR=#000000>19</FONT></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 ><FONT FACE=Arial COLOR=#000000>20</FONT></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 ><FONT FACE=Arial COLOR=#000000>etc</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=6><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>To see the formula in the cells just click on the cells hyperlink</FONT>

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

sen_edp

Well-known Member
Joined
Mar 13, 2002
Messages
555
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,<font color="black"><font size=+1><font color="blue">A<font color="red">ndrea<font color="blue">S</font color="red"><font size=+1><font size=1><font color="red">using xl2000
This message was edited by sen_edp on 2002-09-13 00:32
 

Joel Horowitz

New Member
Joined
Aug 23, 2002
Messages
34
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,334
Messages
5,571,589
Members
412,407
Latest member
ElmerCC
Top