addition of specific range continuously

[Sathisc]

Hi,

I have a data with Dates in Column A and Values in Column B. i need to add 5 values of column B in Cell C2 and in Cell C3 i want to add the next 5 values of column B and so on.

Kinldy help


Sathish

 

[Sathisc]

Hi,

Please find the example given below
<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=199 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #cc99ff; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl29 height=17 width=71 align=right x:num="40132">11/15/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #cc99ff; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl30 width=64 align=right x:num>149</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #cc99ff; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl41 width=64 align=right x:num x:fmla="=SUM(B1:B5)">1576</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #cc99ff; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=17 align=right x:num="40133">11/16/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl32 align=right x:num>455</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 align=right x:num x:fmla="=SUM(B6:B10)">1076</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #cc99ff; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=17 align=right x:num="40134">11/17/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl32 align=right x:num>450</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl46 align=right x:num x:fmla="=SUM(B11:B15)">523</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #cc99ff; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=17 align=right x:num="40135">11/18/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl32 align=right x:num>280</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl53 align=right x:num x:fmla="=SUM(B16:B20)">517</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #cc99ff; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33 height=18 align=right x:num="40136">11/19/2009</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #cc99ff; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl34 align=right x:num>242</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl35 height=17 align=right x:num="40139">11/22/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl36 align=right x:num>654</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl37 height=17 align=right x:num="40140">11/23/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl38 align=right x:num>124</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl37 height=17 align=right x:num="40141">11/24/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl38 align=right x:num>47</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl37 height=17 align=right x:num="40142">11/25/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl38 align=right x:num>242</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39 height=18 align=right x:num="40143">11/26/2009</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl40 align=right x:num>9</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl42 height=17 align=right x:num="40146">11/29/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl43 align=right x:num>336</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl44 height=17 align=right x:num="40147">11/30/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl45 align=right x:num>133</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl44 height=17 align=right x:num="40148">12/1/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl45 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl44 height=17 align=right x:num="40149">12/2/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl45 align=right x:num>15</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl44 height=18 align=right x:num="40150">12/3/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl45 align=right x:num>33</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #ece9d8" class=xl47 height=17 align=right x:num="40153">12/6/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl48 align=right x:num>125</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl49 height=17 align=right x:num="40154">12/7/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl50 align=right x:num>224</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl49 height=17 align=right x:num="40155">12/8/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl50 align=right x:num>87</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl49 height=17 align=right x:num="40156">12/9/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl50 align=right x:num>41</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl51 height=18 align=right x:num="40157">12/10/2009</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl52 align=right x:num>40</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl25 height=17 align=right x:num="40160">12/13/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl26 align=right x:num>42</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 height=17 align=right x:num="40161">12/14/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 height=17 align=right x:num="40162">12/15/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 height=17 align=right x:num="40163">12/16/2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl27 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl23 height=18 align=right x:num="40164">12/17/2009</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl24 align=right x:num>9</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR></TBODY></TABLE>

like wise i need formula to keep on adding the next 5 digits

 

[Andrew Poulsom]

If your data starts in row 1:

=SUM(INDEX(B:B,((ROW(A1)-ROW(A$1))*5)+1):INDEX(B:B,((ROW(A1)-ROW(A$1))*5)+5))

copied down.

 

[Sathisc]

Thanks mate