Dynamically adjusting SUM formula when adding rows at the bottom

[dejhantulip]

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:

So, for instance, as you can see I have a GROUP 1 (G1) and a TOTAL OF GROUP 1 (T1) rows. The basic idea is that cell I10 would SUM(I6:I9) but if I insert a row below Row 9, the sum formula would not include this added row. I remember having watched someone overcome this formula problem with OFFSET or INDIRECT and the result was that the formula would "self-adjust" to include the added row(s)... I can't really remember exactly where I saw this to be honest.

So basically I am looking for a formula that would find, for example, the G1 group and sum all the Total Amount column corresponding to its group which is defined by all the rows that are between G1 and T1, and the same for G2 an T2, and so forth...

I hope I was able to explain what I need, and I thank you in advance for the help!

Best regards,


ORLANDO

 

[mikerickson]

Have you tried converting that into two tables?

To get your fomula 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 I10, the formula =SUM($I$6:aboveMe) will adjust as rows are inserted or deleted, similarly for =SUM($K$6:aboveMe) in K10

 

[dejhantulip]

Have you tried converting that into two tables?

To get your fomula 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 I10, the formula =SUM($I$6:aboveMe) will adjust as rows are inserted or deleted, similarly for =SUM($K$6:aboveMe) in K10

Wow... so simple and SO elegant.

Thank very much sir!

 

[lumahai]

i know this thread is old, but i pretty much have the same situation. For the above, wouldnt putting the Sum formula as =Sum$I$6:aboveme, just result in the sum only accounting for rows above I6? Not seeing where that formula addresses the actual question to having a formula encompass any rows added above the total line.

Appreciate any education!

 

[dreid1011]

i know this thread is old, but i pretty much have the same situation. For the above, wouldnt putting the Sum formula as =Sum$I$6:aboveme, just result in the sum only accounting for rows above I6? Not seeing where that formula addresses the actual question to having a formula encompass any rows added above the total line.

Appreciate any education!

Because the cell referencing in the defined name is relative, it adjusts itself to accommodate whatever cell it is placed in. Because the name is defined in cell B2 with a relative reference to B1 (one cell above), it will adjust itself to whatever cell you place it in, referencing the cell immediately above it. If the reference was absolute ($B$1), then it would always target B1 and not work. It took me a moment to understand how it was working too. But it is very clever. IE: If you place it in cell I10, then the reference points to I9 after it adjusts for where you put it.

 

[lumahai]

Because the cell referencing in the defined name is relative, it adjusts itself to accommodate whatever cell it is placed in. Because the name is defined in cell B2 with a relative reference to B1 (one cell above), it will adjust itself to whatever cell you place it in, referencing the cell immediately above it. If the reference was absolute ($B$1), then it would always target B1 and not work. It took me a moment to understand how it was working too. But it is very clever. IE: If you place it in cell I10, then the reference points to I9 after it adjusts for where you put it.

Wow! I would not have figured that out. I have a long way too go. Thanks for the education around that. A cool trick for sure.
Many thanks!