Sum upward until BLANK CELL

[smartkrips]

Hello All,

I am looking for a formula that will add all numbers from the cell just above it, till the blank cell comes.

I have tried to find if this was already asked or if answer is available and what I got is as below:

Formula: To add all numbers till blank cell comes... but this is for downward calculation...

=SUM(A1:INDEX(A:A,MATCH(TRUE,INDEX(A1:A1000="",0),0)))

Next, for upward, there was an answer to use AutoSum... but then i can't use it as copy paste to another cell and expect it to behave same way... Because, if i use it as copy paste, then it will take same no. cells into consideration that we have in the source.
2nd: if there are 5 continuous rows where numbers are there, and if i add any new row in between it does not re-calculate even if one blank row is added.

Let me know if my question is not clear and you need more information to help me.


Rgds,
KK

 

[Aladin Akyurek]

Why are you talking solutions instead of clearly formulating the problem you have?

A2:A9 >>

3
blank
4
7
3
blank
2
1

Given A2:A9, what is the figure you are looking for?

 

[smartkrips]

Why are you talking solutions instead of clearly formulating the problem you have?

A2:A9 >>

3
blank
4
7
3
blank
2
1

Given A2:A9, what is the figure you are looking for?

Hello Aladin,

Please see below expected result:

As A2 is blank, I will add formula to B2 and it should calculate sum = 3. As there is only one cell above A2(Blank) cell.
Next formula will be in B6 as A6 is blank, and should calculate 14. As A3:A5 = 4 + 7 + 3 = 14.
Next and last, B9 = 3, i.e. A7:A8 = 2 + 1 = 3.

If i add any line between, A7 and A8, it should recalculate B9 to 1. As there is blank space above A8.

If I copy formula to D7 considering numbers are there from C3 to C6 and C2 is blank.. then is should give correct sum - adding all the numbers from C3 to C6.

Let me know if you need any further information/ clarification.


Rgds,
KK

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(INDEX(A:A,MATCH(9.99999999999999E+307,
    1/ISBLANK(INDEX(A:A,1):INDEX(A:A,MATCH(9.99999999999999E+307,A:A))))):
      INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

 

[smartkrips]

Control+shift+enter, not just enter:

=SUM(INDEX(A:A,MATCH(9.99999999999999E+307,
    1/ISBLANK(INDEX(A:A,1):INDEX(A:A,MATCH(9.99999999999999E+307,A:A))))):
      INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

Thanks Aladin... when i tried this formula in B4... it gives sum value of last string in column A. So as per data that you sent above, it will give 3 A7 = 2 + A6 = 1 = B9 = 3.

Now if i leave A9 and insert 1, 2, 3 in A10, A11, A12.. in B4, it will show 6 (1 + 2 + 3 = 6) as this is last continuous numbers in the column...

Am I making any mistake in using the formula?


Rgds,
KK

 

[Aladin Akyurek]

Thanks Aladin... when i tried this formula in B4... it gives sum value of last string in column A. So as per data that you sent above, it will give 3 A7 = 2 + A6 = 1 = B9 = 3.

Now if i leave A9 and insert 1, 2, 3 in A10, A11, A12.. in B4, it will show 6 (1 + 2 + 3 = 6) as this is last continuous numbers in the column...

Am I making any mistake in using the formula?


Rgds,
KK

Why don't you come up yourself with a sample along with the result(s) that must obtain?

 

[smartkrips]

Sorry, if i troubled you so much... Please see below data... Hope this clarifies the requirements. Column# 2, 4, 6 should have the formula to add numbers from the previous column and a cell above till all the cells until blank cell is found.

Money Spend Per Task	Total Money Spend Per Month	Money Spend Per Task	Total Money Spend Per Month	Money Spend Per Task	Total Money Spend Per Month
1.00		3.00		2.00
2.00		1.00		3.00
3.00		2.00		4.00
	6.00	3.00			9.00
3.00		4.00		5.00
4.00		5.00		6.00
5.00		6.00		78.00
	12.00		24.00		89.00
5.00		4.00		3.00
6.00		5.00		4.00
7.00		7.00		5.00
3.00		2.00		2.00
4.00		3.00		3.00
5.00		4.00			17.00
8.00		56.00
	38.00		81.00
		2.00
		3.00
		4.00
			9.00

<tbody>
</tbody>

Let me know if any further questions.


Rgds,
KK

 

[Aladin Akyurek]

In B2 control+shift+enter, not just enter, and copy down:

=IF(A2="",IFERROR(1/(1/SUM(A2:INDEX(A$1:A1,MATCH(9.99999999999999E+307,1/(1-ISNUMBER(A$1:A1)))))),""),"")

Adapt the ranges for the other columns where you need additional calculations.

 

[smartkrips]

Hello Aladin,

Thanks for all your time and effort... but seems I am making some mistake. I see that when i enter all numbers from A1 to A7 and changed the range as desired, pasted formula in B8, it shows blank cell... B8 = Blank...


Rgds,
KK

 

[Aladin Akyurek]

Hello Aladin,

Thanks for all your time and effort... but seems I am making some mistake. I see that when i enter all numbers from A1 to A7 and changed the range as desired, pasted formula in B8, it shows blank cell... B8 = Blank...


Rgds,
KK

Try to follow the instructions: the formula must be entered in B2, confirmed with control+shift+enter, not just enter, and copied down.

Then in D2 you enter, adjust to the C-range, apply control+shift+enter, and copy down...