Automatically adjusting SUM formula when adding rows at the bottom

[lumahai]

Hello everyone!

I need some help figuring out how to work out a SUM formula that would "dynamically adjust" itself "auto adjust" itself when rows are added at the bottom of the sum range I have right now defined.

Here is a screenshot illustration:

For example, as you can see I have a Hardware infra total for Baseline and Adjustment columns. The basic idea is that cell D10 would SUM(D7:D9) but if I insert a row below Row 9, the sum formula would not include this added row. I remember a formula around with OFFSET or INDIRECT and the result was that the formula would "self-adjust" to include the added row(s). But i dont remember the details into it.

I saw another thread mention you could do a named range type of setup, to follow is the mention....

"To get your formula to work that way, you could use a Named Range

Select cell B2 and define a name, Name:aboveMe RefersTo: =Sheet1!B1

Adjust the sheet name as needed, but the relative addressing is critical

Then, in D10, the formula =SUM($I$6:aboveMe) will adjust as rows are inserted or deleted, similarly for =SUM($K$6:aboveMe) in F10"

Link: Dynamically adjusting SUM formula when adding rows at the bottom


However, I am not sure if the above mention actually works for the request I am seeking. I tried it and it would only update the SUM when rows are added above D6, not D10.
I could definitely be wrong in that assumption and not as well educated.

So again, basically I am looking for a formula that would sum all the Total amount row corresponding when rows are added just above, or generally above the total row.

Any further clarification just let me know! Any education is appreciated.

 

[Domenic]

Try...

D10:

=SUM(D7:INDEX(D:D,ROW()-1))

Hope this helps!

 

[lumahai]

Try...

D10:

=SUM(D7:INDEX(D:D,ROW()-1))

Hope this helps!

So far this seems to be working! Thank you for the help. I am curious, could you give me a quick education as to what the formula is saying? That way i know for future case when this could be applicable. I am trying to build out my knowledge.

Again , thank you this is awesome!

 

[Domenic]

Normally, the INDEX function returns a value from within a range.

So, in your example, INDEX(D:D,ROW()-1) returns the value $26,950.00...

=INDEX(D:D,ROW()-1)

=INDEX(D:D,10-1)

=INDEX(D:D,9)

= $26,950.00

However, when you have D7:INDEX(D:D,ROW()-1), it returns a reference to a range, not a value. So now we have the following...

=SUM(D7:INDEX(D:D,ROW()-1))

=SUM(D7:INDEX(D:D,10-1))

=SUM(D7:INDEX(D:D,9))

=SUM(D7:D9)

=$51,436.00

Hope this helps!

 

[lumahai]

Terrific! Thank you for that break-down. That helpls me quite a bit!

 

[Domenic]

You're very welcome, glad I could help.

Cheers!