vba excel - input a SUM formula into cell that sums cell values downwards until blank cell

[Pianostool]

Hi folks.

I have written some code to allow data input through a form. One of the fields is a $ amount.
Each time an entry gets added through the form, it adds a new row in at Row 7, so all other rows of prior data drop down below it.
I have a total of the $ column in cell C5, and some code that totals it all OK as rows get added, as part of the form code.
The $ values are in Column B and the new record gets added each time to Row 7. So the newest dollar value is at B7, and all other $ values from previous entries are below it in column B, but obviously the list grows.
So my code total in C5 works fine, giving me the correct answer, but it's just a figure (the code is doing the formula itself).
I'm wondering how I might get the SUM formula input into C5 via the code, so it's adding from B7 down to the first blank cell in column B?
I need it as a formula rather than a figure, as the users may change one of the $ values in column B later, manually, and this will not update the total if it's just a figure from the last form input run.
I hope that makes sense.
Thanks in advance.

 

[kvsrinivasamurthy]

In C5. ARRAY formula.

=SUM(B7:INDEX(B7:B2000,MATCH(2,1/(B7:B2000<>""),1)))

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.
I don't know which version excel you are using.

 

[Pianostool]

In C5. ARRAY formula.

=SUM(B7:INDEX(B7:B2000,MATCH(2,1/(B7:B2000<>""),1)))

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.
I don't know which version excel you are using.

Hi there, thanks so much for the quick reply.
Unfortunately, this formula keeps starting at B8 instead of B7 each time a new row of data gets input through the form, into Row 7.
So it's not remaining with starting at B7. The ending cell seems to adjust correctly to the last new cell, so that looks good.
I tried adjusting the formula to have absolute references to B7 (changed any B7's in formula to $B$7) but it still changes to B8 when new row goes in.
I was hoping for some VBA to add to my code that would enter a formula like yours into the C5 cell so it's correct every time. Is there a way to add your formula as an array via code, as it will then be entered every time with B7 as the starting point.
Thanks so much.

 

[Peter_SSs]

Change the formula to

=SUM(B6:B9)

This starts the sum at the heading cell (B6) but the sum ignores that cell because it is text.
When the new row 7 is inserted, the bottom cell ref moves down one as you mentioned, but the top cell ref will remain at the heading cell B6, thereby automatically including the new B7 value in the sum result.

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Pianostool]

Change the formula to

=SUM(B6:B9)

This starts the sum at the heading cell (B6) but the sum ignores that cell because it is text.
When the new row 7 is inserted, the bottom cell ref moves down one as you mentioned, but the top cell ref will remain at the heading cell B6, thereby automatically including the new B7 value in the sum result.

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Peter, thanks for both tips.
Haha, it is obvious when you see it - sorry, I should've tried changing it to B6 seeing it was always missing by one row only.
Have tried this and can confirm all works well.
I have also updated my profile as suggested.
Thanks to both of you.
Legends.

 

[kvsrinivasamurthy]

Try

=SUM($B$7:INDEX($B$7:B2000,MATCH(2,1/($B$7:$B$2000<>""),1)))

 

[kvsrinivasamurthy]

Try

=SUM($B$7:INDEX($B$7:$B$2000,MATCH(2,1/($B$7:$B$2000<>""),1)))

 

[Peter_SSs]

Haha, it is obvious when you see it - sorry, I should've tried changing it to B6 seeing it was always missing by one row only.
Have tried this and can confirm all works well.

You're welcome. Only needs a simple formula. Thanks for the follow-up.

I have also updated my profile as suggested.

Thanks for doing that.

 

[Pianostool]

Try

=SUM($B$7:INDEX($B$7:$B$2000,MATCH(2,1/($B$7:$B$2000<>""),1)))

Hi kvsrinivasamurthy, thanks again for your input.
I had marked your original formula as the Solution, as it just needed Peter's adjustment to take the starting cell back one.
However, after I had tested it adding some more data in via the form, which all worked OK, I then went and add a different bit of data in the 2nd cell below the last row.
Your formula does not stop summing at the first blank cell on its way down the column, it continues to add up everything in the column.
I need to it to stop summing once it hits a blank cell.
If you refer to my attachment, you can see your total is $37,000 for SAVINGS, but it should be $36,000. It's adding the $1,000 from the new section called OTHER ASSETS.
I need it to stop at the first blank cell (in this case B15) but it will change to B16 after the next form input of data.
Does that all make sense?
@Peter_SSs - please note.
Thanks.

​

 

[kvsrinivasamurthy]

If you are having AGGREGATE function

=SUM($B$7:INDEX($B$7:$B$2000,AGGREGATE(15,6,ROW($B$7:$B$2000)/($B$7:$B$2000=""),1)-ROW($B$7)))

else
Array formula

=SUM($B$7:INDEX($B$7:$B$2000,SMALL(IF($B$7:$B$2000="",ROW($B$7:$B$2000),""),1)-ROW($B$7)))