SUM Function

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

 

[Guest]

Try this
SUM($B$1:$B$6)

 

[Guest]

They still moved....

Try this
SUM($B$1:$B$6)

 

[Guest]

Perhaps this will work:

=SUM(INDIRECT(ADDRESS(1,2)&":"&ADDRESS(6,2)))

HTH,
Elkar

 

[David Biddulph]

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

 

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

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.

 

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

 

[Guest]

Why not

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

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

 

[Ragdyer]

Why not?

Simple ... I just didn't think about it!<bg>

 

[Guest]

That worked, thanks!!

Why not

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