Sum until blank cell

J

jessica_p

New Member
Joined
Aug 17, 2018
Messages
10
Hi,

I've been looking on this for a while and can't seem to figure it out. I've fried looking for a solution on the internet, but it won't help.
So, in column E I want to sum up all amounts in C until a blank cell is reached. So for cell E3 I want to sum up 195,97 and for cell E8 I want to sum up 272,96 until 136,48.

ABCDE
1BenefitEmp.noBoughtBudgetTotal bought
2Buy additional leave156 570,78
3 195,97 0
4Bicycles8499 464,50
5Buy additional leave85277 439,21
6Buy additional leave95 84,37
7Buy additional leave82222 704,30
8Buy additional leave98322 265,53
9 272,96 0
10 226,38 0
11 136,48 0
12Planning my pension98226 136,48
13Buy additional leave89577 428,03
14Buy additional leave89468 216,18
15Buy additional leave89668 705,07
16Planning my pension87519 320,00
17Fitshop85532 150,00
18Planning my pension87958 405,40

<colgroup><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>


What's the formula for this?
It seems like =IF(E3="";SUMIF(D:D;D3;E:E);"") won't do the trick.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Welcome to the MrExcel board!

Showing results that you do want is much more useful than showing results you don't want. ;)

If this is not it, please show the expected results and explain how you got them manually.
Note that you may need to swap "," for ";" in my formula for your regional version of excel.

Excel Workbook
CDE
1BoughtBudgetTotal bought
2570.78 
3195.97195.97
4464.5
5439.21
684.37
7704.3
8265.53
9272.96468.93
10226.38695.31
11136.48831.79
12136.48
13428.03
14216.18
15705.07
16320
17150
18405.4
Sum
 
Last edited:
Upvote 0
Just my interpretation of what was posted:

=IF(AND(NOT(ISBLANK(C3)), ISBLANK(C4)), SUM($C$2:C3)-SUM($E$2:E2), "")

Copied in E3 and copied down.

Like Peter mentioned you would have to swap "," for ";".
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

Showing results that you do want is much more useful than showing results you don't want. ;)

If this is not it, please show the expected results and explain how you got them manually.
Note that you may need to swap "," for ";" in my formula for your regional version of excel.

Sum

CDE
1BoughtBudgetTotal bought
2 570.78
3195.97 195.97
4 464.5
5 439.21
6 84.37
7 704.3
8 265.53
9272.96 468.93
10226.38 695.31
11136.48 831.79
12 136.48
13 428.03
14 216.18
15 705.07
16 320
17 150
18 405.4

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:60px;"><col style="width:71px;"><col style="width:90px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
E2=IF(C2="","",SUM(C$2:C2))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Thank you Peter. Only thing with this formula is that it continues the sum throughout column E.
What I'm looking for, is to stop the sum and start a new sum once a blank cell is reached. So for this table above, I want Excel to make the sum on cell C3 and than start over, so the new sum is SUM(C9:C11), after which starting the sum over again. Is htis a possibility? Otherwise the formula in E would have been easy.
 
Upvote 0
Is this what you want?


Book1
ABCDE
1BenefitEmp.noBoughtBudgetTotal bought
2Buy additional leave156570,78 
3195.97195.97
4Bicycles8499464,50 
5Buy additional leave85277439,21 
6Buy additional leave9584,37 
7Buy additional leave82222704,30 
8Buy additional leave98322265,53 
9272.96272.96
10226.38499.34
11136.48635.82
12Planning my pension98226136,48 
13Buy additional leave89577428,03 
14Buy additional leave89468216,18 
15Buy additional leave89668705,07 
16Planning my pension87519320,00 
17Fitshop85532150,00 
18Planning my pension87958405,40 
Sheet2
Cell Formulas
RangeFormula
E2=IF(C2="","",IF(F1="",C2,F1+C2))
E3=IF(C3="","",IF(E2="",C3,E2+C3))
E4=IF(C4="","",IF(E3="",C4,E3+C4))
E5=IF(C5="","",IF(E4="",C5,E4+C5))
E6=IF(C6="","",IF(E5="",C6,E5+C6))
E7=IF(C7="","",IF(E6="",C7,E6+C7))
E8=IF(C8="","",IF(E7="",C8,E7+C8))
E9=IF(C9="","",IF(E8="",C9,E8+C9))
E10=IF(C10="","",IF(E9="",C10,E9+C10))
E11=IF(C11="","",IF(E10="",C11,E10+C11))
E12=IF(C12="","",IF(E11="",C12,E11+C12))
E13=IF(C13="","",IF(E12="",C13,E12+C13))
E14=IF(C14="","",IF(E13="",C14,E13+C14))
E15=IF(C15="","",IF(E14="",C15,E14+C15))
E16=IF(C16="","",IF(E15="",C16,E15+C16))
E17=IF(C17="","",IF(E16="",C17,E16+C17))
E18=IF(C18="","",IF(E17="",C18,E17+C18))
 
Upvote 0
If this is not it please show the expected results and layout.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

Excel Workbook
ABCDE
1BenefitEmp.noBoughtBudgetTotal bought
2Buy additional leave156570.78 
3195.97195.97
4Bicycles8499464.5
5Buy additional leave85277439.21
6Buy additional leave9584.37
7Buy additional leave82222704.3
8Buy additional leave98322265.53
9272.96272.96
10226.38499.34
11136.48635.82
12Planning my pension98226136.48
13Buy additional leave89577428.03
14Buy additional leave89468216.18
15Buy additional leave89668705.07
16Planning my pension87519320
17Fitshop85532150
18Planning my pension87958405.4
Sum
 
Last edited:
Upvote 0
@Scot T
If that is the MrExcel HTML Maker, then please look at the 'Generate Html (specify option)' choices to restrict the number of formulas generated. There is generally no need to display multiple formulas that are basically the same, it just fills up the board and makes your post and the thread hard to read/navigate.
 
Upvote 0
Sorry, still not what I'm looking for although I really appreciate the help.
But still hope it's possible wihout using macro's as I'm not yet known with macro's.

What result I want:

ABCDE
1BenefitEmp.noBoughtBudgetTotal bought
2Buy additional leave156 570,78 195,97
3 195,97
4Bicycles8499 464,50
5Buy additional leave85277 439,21
6Buy additional leave95 84,37
7Buy additional leave82222 704,30
8Buy additional leave98322 265,53 635,82
9 272,96
10 226,38
11 136,48
12Planning my pension98226 136,48
13Buy additional leave89577 428,03 56,37
14 56,57
15Buy additional leave89468 216,18
16Buy additional leave89668 705,07 251,92
17 125,96
18 125,96
19Planning my pension87519 320,00
20Fitshop85532 150,00
21Planning my pension87958 405,40

<colgroup><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>


Where:
Column E is the result of bought per employee number.
So E2 is the result of the amount bought by emp. 156 (cel C3).
The employees in C4 - C7 didn't buy, so E4 - E7 have to stay empty.
The employee in C8 bought (cell C9 - C11), which in total for that employee comes down to 635,82 (272,96+226,38+136,48) in cel E8.
And so on.

If it would be possible to insert a formula for this, if would be great. Otherwise I'll have to manually add a calculation for all 900 employees:)
 
Upvote 0
Thanks, that's clearer.
Try this, copied down. Note that the results will not be correct until you do copy the formula down the column.

<b>Sum</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:143px;" /><col style="width:64px;" /><col style="width:61px;" /><col style="width:61px;" /><col style="width:90px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Benefit</td><td style="font-size:10pt; text-align:right; ">Emp.no</td><td style="font-size:10pt; ">Bought</td><td style="font-size:10pt; text-align:right; ">Budget</td><td style="font-size:10pt; text-align:right; ">Total bought</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Buy additional leave</td><td style="font-size:10pt; text-align:right; ">156</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">570.78</td><td style="font-size:10pt; text-align:right; ">195.97</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">195.97</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Bicycles</td><td style="font-size:10pt; text-align:right; ">8499</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">464.5</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Buy additional leave</td><td style="font-size:10pt; text-align:right; ">85277</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">439.21</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">Buy additional leave</td><td style="font-size:10pt; text-align:right; ">95</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">84.37</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; ">Buy additional leave</td><td style="font-size:10pt; text-align:right; ">82222</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">704.3</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">Buy additional leave</td><td style="font-size:10pt; text-align:right; ">98322</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">265.53</td><td style="font-size:10pt; text-align:right; ">635.82</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">272.96</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">226.38</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">136.48</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; ">Planning my pension</td><td style="font-size:10pt; text-align:right; ">98226</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">136.48</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; ">Buy additional leave</td><td style="font-size:10pt; text-align:right; ">89577</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">428.03</td><td style="font-size:10pt; text-align:right; ">56.57</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">56.57</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; ">Buy additional leave</td><td style="font-size:10pt; text-align:right; ">89468</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">216.18</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; ">Buy additional leave</td><td style="font-size:10pt; text-align:right; ">89668</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">705.07</td><td style="font-size:10pt; text-align:right; ">251.92</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">125.96</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">125.96</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; ">Planning my pension</td><td style="font-size:10pt; text-align:right; ">87519</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">320</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:10pt; ">Fitshop</td><td style="font-size:10pt; text-align:right; ">85532</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">150</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:10pt; ">Planning my pension</td><td style="font-size:10pt; text-align:right; ">87958</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">405.4</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=IF(AND<span style=' color:008000; '>(C2="",C3<>"")</span>,SUM<span style=' color:008000; '>(C3:C$30)</span>-SUM<span style=' color:008000; '>(E3:E$30)</span>,"")</td></tr></table></td></tr></table>
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,411
Messages
6,119,346
Members
448,888
Latest member
Arle8907

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