SUM Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I stop the function from moving if I insert cells? For example, if my
function is SUM(B1:B6) and I insert cells it changes it to SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)
 
How do I stop the function from moving if I insert cells? For example, if my
function is SUM(B1:B6) and I insert cells it changes it to SUM(B2:B7).....

I'd like it to stay SUM(B1:B6)
Click to expand...

An obvious question is why would you insert cells in this range when you
know you have formulas referring to it? There are often better approaches
than frequent insertion or deletion.

Still, as an academic exercise, the simplest approach would be

=SUM(INDIRECT("B1:B6"))

The INDIRECT function is volatile, so this formula would recalculate every
time anything triggers recalc. If you have lots of such formulas, it could
noticeably slow recalculation.
 
BUT ... if you're *only* going to insert rows at *Row1*, you can try this
non-volatile formula:

=SUM(INDEX(B:B,ROWS($1:$1)):INDEX(B:B,ROWS($1:$6)))
 
Why not

=SUM(INDEX(B:B,1):INDEX(B:B,6))



Ragdyer said:
BUT ... if you're *only* going to insert rows at *Row1*, you can try this
non-volatile formula:

=SUM(INDEX(B:B,ROWS($1:$1)):INDEX(B:B,ROWS($1:$6)))
Click to expand...
 
Back
Top