Sum formulas do NOT expand when rows added !

[Blue Max]

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

 

[Arvi Laanemets]

Hi

There are 2 possible solutions.

1) When there are no numeric values outside SUM range in column, you can
use column reference, like
=SUM(A:A);

2) Use dynamic named ranges. An example:
You have a table on sheet Data, where in 1st row are table headers, like
A1="Field1", A2="Field2", A3="Field", etc., and actual data start from row2.
Define a dynamic range (Insert>Name>Define)
Range3=OFFSET(Data!$C$1,1,,COUNTA(Data!$A:$A)-1,1)
Now you can sum all data in column Field1 using formula =SUM(Range3), and
the range adjusts automatically when you edit the table. Only restrictions
are, that there must not be any entries in column A except in your table,
that there are left no gaps in table column Field1, and than the header row
is never deleted.

 

[Lori]

Suppose you wanted to sum from A2 up to the current cell and keep this stays
fixed after insert/delete. You could use defined names and enter in A12, say,

=SUM(start:end)

with start: "=!A$2" and end: "=A11".
(Omitting the $s means these are defined relative to the current cell. The
"!" ensures that A2 remains fixed after insert/delete but should be omitted
from end so that it refers to the current sheet)

Alternatively, try:

=SUM(INDEX(A:A,2):INDEX(A:A,ROW()-1))

Unlike offset & indirect these formulas are not volatile so should not slow
down worksheet recalculation.

 

[splot!]

The way I have always done this is to include in the sum a blank row
at the top and bottom of the range. Sometimes I shade the row to
remind me that it's not to have entries in it - or it could be just
made narrow. As it is blank it doesn't affect the calucation. But if
rows are inserted at the extremes of the range of data, they will push
these blank rows out and stretch the formila.

I've only recently discovered dynamic ranges (though this group) but
they seem like a sledgehammer to crack a nut option. This is a
_simple_ way!

Regards

Chris

 

[splot!]

I've just re-read this and I realise it could be interpreted to say
that dynamic ranges are always a sledgehammer option. That wasn't
what I meant. They are really useful and I use them regularly now
I've discovered them. What I meant was that for this particular
problem it seems like a sledgehammer option

 

[Blue Max]

Thank you, Lori, you introduced some novel concepts of which we were
unaware. We were not aware of the significance of the exclamation point "!"
in a cell reference, nor of the fact that the INDEX function was more
efficient than the OFFSET function. One of our reservations with the OFFSET
function has always been the increased overhead that seemed to slow down the
calculations.

Thank you for some great ideas, we will have to digest them a little more to
understand the full scope of their usefulness, but are appreciative of the
new lead!

Thanks,

Richard

*********************