Dynamically adjusting SUM formula when adding rows at the bottom

dejhantulip

Board Regular
Joined
Sep 9, 2015
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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:

OID18nM.png


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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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! :)
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top