Sum/Count Formulas auto adjust for inserted rows

Question

Looking to create a sum and a count formula that will automatically adjust itself for new rows that are inserted within the range.

For example if I have the formula in cell D55 =SUM(D17:D54). Every time I insert a new row within that range, I need to change the top range of my formula to account for it.

Is there a way to write a formula that will automatically adjust itself, so that every time I add a new row I will not need to change my summation formula?

Answer 1

Try

D55: =SUM(INDIRECT("D17:D"&ROW()-1))

This should dynamically adjust to added rows since when adding rows at row 17 the current value at D17 shifts to D18 and no value is present at D17. INDIRECT() should take this into account. ROW()-1 ensures that even when rows are added immediately preceding the formula these are still taken into account.

Edit: I should have added that this can be applied to any formula. Simply replace the range part of your formula with the INDIRECT. And a quick explanation: the INDIRECT creates an Excel reference from a string so you can construct your formula using dynamic objects. The ROW part of the formula acts as the dynamic factor which is completely dependent on the row count that you add but is independent of the position at which you add your new row.