SUM Function

S

Shawn

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)
 
H

Harlan Grove

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.
 
R

Ragdyer

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)))
 
J

JMB

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...
 
Top