Sum up to next blank row

C

collegeitdept

Board Regular
Joined
Nov 14, 2008
Messages
185
Does anyone know of a formula (variable – not locked) that will find the sum of the rows above up to the next blank cell/row?


IE: I have a very large spreadsheet full of data that are broken up into separate sections (quarters/semesters/months) – they are “broken”/separated by a blank row. At the end of every section, I need to sum the rows above for only each section.


So I need a formula that will find the sum of the rows above (from the current row) up to the next blank row.

I found a similar formula here:
{=SUM(INDIRECT("B2:B"&MATCH(TRUE,INDIRECT("B2:B100")="",0)+1))}

But this does not work because it sums down to the first blank row and that it is not variable (it is locked to start at cell B2).

Thanks.
 
Sort by date Sort by votes
Yes, it will:

<b>Excel 2003</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>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Numbers</td><td style=";">Position 1</td><td style=";">Position 2</td><td style=";">Position 3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">7</td><td style="text-align: right;;">6.125</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5</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;">4</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;">5</td><td style="text-align: right;;">10</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;;">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;">7</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;">8</td><td style="text-align: right;;">9</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;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">6.125</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6.125</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3.571429</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;;">2</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;;">2</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;;">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;">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;;">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;">17</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">3.571428571</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3.571429</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">10</td><td style="text-align: right;;">6.714286</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><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">5</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;">22</td><td style="text-align: right;;">8</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;">23</td><td style="text-align: right;;">9</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;">24</td><td style="text-align: right;;">10</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;">25</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">6.714285714</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6.714286</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4.6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</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;">29</td><td style="text-align: right;;">8</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;">30</td><td style="text-align: right;;">7</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;">31</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">4.6</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4.6</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>Array 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">C2</th><td style="text-align:left">{=AVERAGE(<font color="Blue">B2:INDEX(<font color="Red">B2:$B$100,MATCH(<font color="Green">1,IF(<font color="Purple">B2:$B$100="",1,""</font>),0</font>)-1</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">{=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">9.99E+307,IF(<font color="Purple">$B$1:B9="",1,""</font>)</font>)</font>),AVERAGE(<font color="Red">$B$2:B9</font>),AVERAGE(<font color="Red">INDEX(<font color="Green">$B$2:B9,MATCH(<font color="Purple">9.99E+307,IF(<font color="Teal">$B$2:B9="",1,""</font>)</font>)</font>):B9</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E10</th><td style="text-align:left">{=IF(<font color="Blue">ISERROR(<font color="Red">MATCH(<font color="Green">9.99E+307,IF(<font color="Purple">$B$1:B9="",1,""</font>)</font>)</font>),AVERAGE(<font color="Red">$B$2:B9</font>),AVERAGE(<font color="Red">INDEX(<font color="Green">$B$2:B9,MATCH(<font color="Purple">9.99E+307,IF(<font color="Teal">$B$2:B9="",1,""</font>)</font>)</font>):B9</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
MrKowz said:
Try: Excel 2003
BC
1NumbersSum
2749
35
44
510
64
71
89
99
10
11574
122
132
146
156
161
173
18
1910121
203
215
228
239
2410
252
26
272144
283
298
307
313
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead> </thead><tbody> </tbody>
Sheet1
<thead> </thead>
Array Formulas
CellFormula
C2{=SUM($B$2:INDEX(B2:$B$100,MATCH(1,IF(B2:$B$100="",1,""),0)-1))}
C11{=SUM($B$2:INDEX(B11:$B$100,MATCH(1,IF(B11:$B$100="",1,""),0)-1))}
C19{=SUM($B$2:INDEX(B19:$B$100,MATCH(1,IF(B19:$B$100="",1,""),0)-1))}
C27{=SUM($B$2:INDEX(B27:$B$100,MATCH(1,IF(B27:$B$100="",1,""),0)-1))}
<thead> </thead><tbody> </tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself
<tbody> </tbody>
Click to expand...
Hi Just Try these if you follow below steps you no need to use VBA macro and formula Select Column
-- removed inline image ---
Press CTR+G > select Blanks
-- removed inline image ---
Then Go to Home Tab and press Auto Sum Button
-- removed inline image ---

-- removed inline image ---
Thanks.....
 
Upvote 0
MrKowz said:
Try:

Excel 2003
BC
1NumbersSum
2749
35
44
510
64
71
89
99
10
11574
122
132
146
156
161
173
18
1910121
203
215
228
239
2410
252
26
272144
283
298
307
313

<tbody>
</tbody>
Sheet1

<tbody>
</tbody>


Array Formulas
CellFormula
C2{=SUM($B$2:INDEX(B2:$B$100,MATCH(1,IF(B2:$B$100="",1,""),0)-1))}
C11{=SUM($B$2:INDEX(B11:$B$100,MATCH(1,IF(B11:$B$100="",1,""),0)-1))}
C19{=SUM($B$2:INDEX(B19:$B$100,MATCH(1,IF(B19:$B$100="",1,""),0)-1))}
C27{=SUM($B$2:INDEX(B27:$B$100,MATCH(1,IF(B27:$B$100="",1,""),0)-1))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
Click to expand...


Hi

Just Try these
if you follow below steps you no need to use VBA macro and formula

Select Column



Press CTR+G

> select Blanks


Then Go to Home Tab and press Auto Sum Button



Thanks.....
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Sum referenced cells in Sheet range using SUM(INDIRECT()) #REF! ERROR
2
Replies
12
Views
272
Fluff
Fluff
W
  • Solved
Combining Filter function with Indirect/Concatenate to look up from another worksheet
Replies
3
Views
235
Fluff
Fluff
H
  • Question
Avoiding INDIRECT when referencing and comparing ranges in different workbooks
Replies
1
Views
327
RoryA
RoryA
J
  • Question
Cumulative sum from the bottom row
Replies
5
Views
438
awoohaw
awoohaw
E
  • Solved
Summing Values
Replies
3
Views
244
LazyBug
LazyBug

Forum statistics

Threads
1,215,480
Messages
6,125,050
Members
449,206
Latest member
Healthydogs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top