Sum of variable row in Excel

Question

I have a row with several months of values (C43:F43) that I want to sum on the farthest left column (B43).

When I add a new month in Col "C", I want the sum formula to dynamically include that new month in C43, but it moves over to start in Col D43.

I tried INDIRECT and it works to include the new column, but, if I insert a row above row 43, it messes up the formulas.

I then tried the OFFSET formula, but to no avail.

Any insight would be greatly appreciated. I now realize EXCEL is quite advanced.

Thank you.

Answer 1

Easy way: create a column to the right of your total, make it as narrow as possible (or hide it), and do your sum from this column (which will be column C) to the end of your data set. When you add a column (which will always be column D), your SUM will always cover it.

More complicated formula: you can use =SUM(OFFSET(B43,0,1,1,1000))

The offset formula can reference the cell you're in as long as you shift away from it. In the example above, I'm moving zero rows, one column to the right, in a range with height 1 cell and width 1000 cells. You can also if you have to end at column G use

=SUM(OFFSET(B43,0,1,COLUMN(G43)-2)

The G43 will move as you add columns so your formulae will stay in sync

Answer 2

To fix the column, put a $ sign before the column letter. To fix the row, put a $ sign before the line number

=SUM($C$43:F$43)