Sum/Count Formulas auto adjust for inserted rows

9
votes

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?

3
excel
it's already automatically adjustes: if you insert new row between 17 and 54, your formula (in cell D56) would become =SUM(D17:D55) - Dmitry Pavliv
@pnuts, OP said I have the formula in cell D55, wouldn't your formula =Sum(D17:D55) create circular reference? - Dmitry Pavliv

3 Answers

9
votes

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.

1
votes

I think people are misunderstanding. You're inserting a row at the TOP of your range correct?

If so, I would suggest formatting the range as a table. That way your table could look like this:

enter image description here

And the sum function would be simply:

=SUM([Data])

when a row is inserted above the 7, everything is automatically updated.

enter image description here

0
votes

You might want to have a look at this excellent link that talks about using tables to hold your ranges. This would be my recommendation ... wrap your data in a table. This will create a structured yet flexible reference.

As is noted here, and also from experience, "Because table data ranges often change, the cell references for structured references adjust automatically. For example, if you use a table name in a formula to count all the data cells in a table, and you then add a row of data, the cell reference automatically adjusts."