Is there a simple way to make a SUM formula expand to include a new row if
the row is added as the first or last row of the SUM range?

For years we have been frustrated that rows added to the extremes of a range
included in a SUM formula are not automatically included in the range used
in the formula. This is also true of many other functions.

It seems logical to us, that if we select any row within a range, and insert
a new row, the new row should be included in any formula addressing the
original range. On the other hand, if we select a row contingent to, but
outside a range, and add a new row, it is logical that the new row not be
included in the formula addressing the original range. To us this seems a
logical approach and gives the user a predictable way to quickly add rows
(or columns) and know if any forumula referencing the range will, or will
not, be automatically included in the range.

Nevertheless, the user currently has a problem if he/she adds a new row
above the first row or beneath the last row of a range formula range. Such
new rows are not automatically included in the original range reference used
by a formula. This is especially problematic if the user originally defines
a range of one row since a later addition to the range will never be
automatically included in the SUM formula addressing the original range.

Please note that the same problem we have with rows also applies to columns.

As a work-around we have used the OFFSET formula to address a larger range
than the actual SUM range. This works very well, but generates huge
overhead in developing the original formulas or worksheet. We have also
tried adding a phantom row of minimal height (or hidden), between the last
row of a range and the sum formula in a 'Totals' row. This allows the user
to add new rows at the end of the SUM range. But this two generates
significant overhead and complicates the worksheet.

Does anybody know of a simpler approach, or of any configuration change,
that would force new rows to be included in the original range? Thank you
for any suggestions.

Richard