I was wondering if it is possible to make a formula that auto-adjusts its scope based on how the sheet grows. Here's an example to illustrate:

Currently, cell B16 is calculating the sum from B2 to B15. Suppose that in the future, I keep filling up the rows with items and costs and reach cell B15 (Everything is now filled up. If I want to add more items, I will insert a new row at B16 and the 'Total' Row becomes B17.

But the formula will not self adjust, and the range will remain at B2:B15. Is there a way to go about automating the process to self-adjust the formula range when I insert new rows?

I'm new to Excel, but I do have programming experience if that matters. Other alternative methods to go about solving this are also welcome. Thank you!

CodePudding user response：

If it's just the sum you want to calculate, then it's fairly easy, because an empty cell is considered having value 0.

So, you can put your formula in another column and replace it by =SUM(B$2:B$1048576).

Remark:
The value 1048576 is based on my maximum number of rows. You can check that value pressing Ctrl ↓.

If you really want to have the sum in the same column, you might use this formula:

=SUM(B$2:OFFSET(Bxxx,-1,0))

Where Bxxx is the cell reference where you are putting this formula.