Dynamically adjusting SUM formula when adding rows at the bottom

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
30
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
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

New Member
Joined
Sep 9, 2015
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,087
Members
414,426
Latest member
fraru

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
Top