Summing a range of values in an array

[User Name]

Hello... I've got a slow pitch here. I have an array with 100 values it it. How can I pick out and sum a range of values in the array? For example, let's say I'd like to sum value 25 to value 50.

Batter batter?

 

[Glory]

public sub test()
 
Dim a()
 
For i = lbound(a) to ubound(a)
 
   If a(i) > 25 and a(i) < 50 Then
      b = b + a(i)
   End if
 
Next
 
MsgBox b
 
end sub

 

[MrKowz]

SWING Batter Batter!

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style=";">Excel 2003 or Older</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Lower Bound</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style=";">Upper Bound</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style=";">Sum</td><td style="text-align: right;;">27</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style=";">Excel 2007 or Newer</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style=";">Lower Bound</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style=";">Upper Bound</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style=";">Sum</td><td style="text-align: right;;">27</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A1:A20>=D2</font>),--(<font color="Red">A1:A20<=D3</font>),A1:A20</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">=SUMIFS(<font color="Blue">A:A,A:A,">="&D2,A:A,"<="&D3</font>)</td></tr></tbody></table></td></tr></table><br />

 

[T. Valko]

Hello... I've got a slow pitch here. I have an array with 100 values it it. How can I pick out and sum a range of values in the array? For example, let's say I'd like to sum value 25 to value 50.

Batter batter?

Do you mean that if the range of numbers is in A1:A100, you want to get the sum of the range A25:A50?

If that's what you want...

Use cells to hold the range boundaries:

• C1 = lower range boundary = 25
• D1 = upper range boundary = 50

Then:

=SUM(INDEX(A1:A100,C1):INDEX(A1:A100,D1))

 

[User Name]

Shoot... So sorry... I need to do the operations in VBA and I should have been more specific about that. Thanks for the code Glory.