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

 

[smartkrips]

Ok, it worked now...thank you so much. It seems for formula to work, there has to be one blank cell in the previous column.. if I have number starting from A1... till A8... this formula will not work. Must have one cell blank before I actually start the numbers.


Rgds,
KK

 

[Aladin Akyurek]

Ok, it worked now...thank you so much. It seems for formula to work, there has to be one blank cell in the previous column.. if I have number starting from A1... till A8... this formula will not work. Must have one cell blank before I actually start the numbers.


Rgds,
KK

I have no idea what you are trying to say...

 

[smartkrips]

Thank you very very much... I see that for formula to work at least one of the cell in the previous column has to be non-numbered. It can be blank or some text data - like i have header for the column. If numbers are starting from A1 to A7 and if I type formula in B8 then it will not give any output... Just FYI or something that I noticed.

Once again many many thanks for your all time and effort. Much appreciated.


Rgds,
KK

 

[bosco_yip]

Another option,

In B2, enter normal formula and copy down :

=IF((A2="")*(A1<>""),SUM(A$1:A2)-SUM(B$1:B1),"")

Regards

 

[Aladin Akyurek]

Thank you very very much... I see that for formula to work at least one of the cell in the previous column has to be non-numbered. It can be blank or some text data - like i have header for the column. If numbers are starting from A1 to A7 and if I type formula in B8 then it will not give any output... Just FYI or something that I noticed.

Once again many many thanks for your all time and effort. Much appreciated.


Rgds,
KK

See bosco_yip's proposal...

 

[smartkrips]

Hello bosco_yip... this formula seems really magical... I could successfully use Aladin's formula as I needed... however, that was highly technical for me, due to my knowledge gap... this one i could understand...

Thanks a lot...


Rgds,
KK

 

[bosco_yip]

Hello bosco_yip... this formula seems really magical... I could successfully use Aladin's formula as I needed... however, that was highly technical for me, due to my knowledge gap... this one i could understand...

Thanks a lot...


Rgds,
KK

Glad to help

Regards

 

[ExcelKnut]

That formula made my brain hurt. How would I adjust that formula to have the result populate in the same row as the last number rather than the next row?

Any help is greatly appreciated!

Jeff

 

[ExcelKnut]

Just now seeing bosco_yip's suggestion and I was able to update that formula to suit my needs. Much simpler solution for my simple mind.
Thanks very much!

Jeff

 

[felixstraube]

Would this work?:

Book7.xlsx
	A
1
2
3
4
5	Header
6	100
7
8	1000
9
10	2
11	3
12	4
13	5
14	14
Sheet2

Cell Formulas
Range	Formula
A14	A14	=SUM(INDEX(A1:A13, MAX(ROW(A1:A13)*(A1:A13=""))):A13)