Sum up to next blank row

[collegeitdept]

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.

 

[MrKowz]

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 />

 

[collegeitdept]

Thanks so much for your help.

Your formulas worked perfectly.

 

[suhask11]

Try: Excel 2003

	B	C
Numbers	Sum

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead> </thead><tbody> [TD="align: center"]1[/TD] [TD="align: center"]2[/TD] [TD="align: right"]7[/TD] [TD="align: right"]49[/TD] [TD="align: center"]3[/TD] [TD="align: right"]5[/TD] [TD="align: right"][/TD] [TD="align: center"]4[/TD] [TD="align: right"]4[/TD] [TD="align: right"][/TD] [TD="align: center"]5[/TD] [TD="align: right"]10[/TD] [TD="align: right"][/TD] [TD="align: center"]6[/TD] [TD="align: right"]4[/TD] [TD="align: right"][/TD] [TD="align: center"]7[/TD] [TD="align: right"]1[/TD] [TD="align: right"][/TD] [TD="align: center"]8[/TD] [TD="align: right"]9[/TD] [TD="align: right"][/TD] [TD="align: center"]9[/TD] [TD="align: right"]9[/TD] [TD="align: right"][/TD] [TD="align: center"]10[/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: center"]11[/TD] [TD="align: right"]5[/TD] [TD="align: right"]74[/TD] [TD="align: center"]12[/TD] [TD="align: right"]2[/TD] [TD="align: right"][/TD] [TD="align: center"]13[/TD] [TD="align: right"]2[/TD] [TD="align: right"][/TD] [TD="align: center"]14[/TD] [TD="align: right"]6[/TD] [TD="align: right"][/TD] [TD="align: center"]15[/TD] [TD="align: right"]6[/TD] [TD="align: right"][/TD] [TD="align: center"]16[/TD] [TD="align: right"]1[/TD] [TD="align: right"][/TD] [TD="align: center"]17[/TD] [TD="align: right"]3[/TD] [TD="align: right"][/TD] [TD="align: center"]18[/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: center"]19[/TD] [TD="align: right"]10[/TD] [TD="align: right"]121[/TD] [TD="align: center"]20[/TD] [TD="align: right"]3[/TD] [TD="align: right"][/TD] [TD="align: center"]21[/TD] [TD="align: right"]5[/TD] [TD="align: right"][/TD] [TD="align: center"]22[/TD] [TD="align: right"]8[/TD] [TD="align: right"][/TD] [TD="align: center"]23[/TD] [TD="align: right"]9[/TD] [TD="align: right"][/TD] [TD="align: center"]24[/TD] [TD="align: right"]10[/TD] [TD="align: right"][/TD] [TD="align: center"]25[/TD] [TD="align: right"]2[/TD] [TD="align: right"][/TD] [TD="align: center"]26[/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: center"]27[/TD] [TD="align: right"]2[/TD] [TD="align: right"]144[/TD] [TD="align: center"]28[/TD] [TD="align: right"]3[/TD] [TD="align: right"][/TD] [TD="align: center"]29[/TD] [TD="align: right"]8[/TD] [TD="align: right"][/TD] [TD="align: center"]30[/TD] [TD="align: right"]7[/TD] [TD="align: right"][/TD] [TD="align: center"]31[/TD] [TD="align: right"]3[/TD] [TD="align: right"][/TD] </tbody>

Sheet1

<thead> </thead>

[TABLE="width: 85%"] <tbody>[TR] [TD]Array Formulas[TABLE="width: 100%"] <thead>[TR="bgcolor: #E0E0F0"] [TH="width: 10px"]Cell[/TH] [TH="align: left"]Formula[/TH] [/TR] </thead><tbody>[TR] [TH="width: 10px, bgcolor: #E0E0F0"]C2[/TH] [TD="align: left"]{=SUM($B$2:INDEX(B2:$B$100,MATCH(1,IF(B2:$B$100="",1,""),0)-1))}[/TD] [/TR] [TR] [TH="width: 10px, bgcolor: #E0E0F0"]C11[/TH] [TD="align: left"]{=SUM($B$2:INDEX(B11:$B$100,MATCH(1,IF(B11:$B$100="",1,""),0)-1))}[/TD] [/TR] [TR] [TH="width: 10px, bgcolor: #E0E0F0"]C19[/TH] [TD="align: left"]{=SUM($B$2:INDEX(B19:$B$100,MATCH(1,IF(B19:$B$100="",1,""),0)-1))}[/TD] [/TR] [TR] [TH="width: 10px, bgcolor: #E0E0F0"]C27[/TH] [TD="align: left"]{=SUM($B$2:INDEX(B27:$B$100,MATCH(1,IF(B27:$B$100="",1,""),0)-1))}[/TD] [/TR] </tbody>[/TABLE] Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself[/TD] [/TR] </tbody>[/TABLE]

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.....

 

[suhask11]

Try:

Excel 2003

	B	C
Numbers	Sum

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]49[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]74[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]121[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]144[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1

<tbody>
</tbody>

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C2[/TH]
[TD="align: left"]{=SUM($B$2:INDEX(B2:$B$100,MATCH(1,IF(B2:$B$100="",1,""),0)-1))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C11[/TH]
[TD="align: left"]{=SUM($B$2:INDEX(B11:$B$100,MATCH(1,IF(B11:$B$100="",1,""),0)-1))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C19[/TH]
[TD="align: left"]{=SUM($B$2:INDEX(B19:$B$100,MATCH(1,IF(B19:$B$100="",1,""),0)-1))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C27[/TH]
[TD="align: left"]{=SUM($B$2:INDEX(B27:$B$100,MATCH(1,IF(B27:$B$100="",1,""),0)-1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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.....