Summation of Rows Problem

T

Tom

I have two worksheets and have created a formula to sum cells ie
sum(G4:G200)from worksheet A in worksheet B. However when I delete or
add rows from worksheet A the formula changes accordingly. Is there a
way to lock the formula so that it remains as sum(G4:G200) even if rows
with the range are added or deleted in worksheet A.

Thanks
 
R

RagDyeR

Couple of ways:

=SUM(INDIRECT("Sheet1!G4:G200"))

And, non-volatile:

=SUM(INDEX(Sheet1!G:G,ROW(4:4)):INDEX(Sheet1!G:G,ROW(200:200)))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I have two worksheets and have created a formula to sum cells ie
sum(G4:G200)from worksheet A in worksheet B. However when I delete or
add rows from worksheet A the formula changes accordingly. Is there a
way to lock the formula so that it remains as sum(G4:G200) even if rows
with the range are added or deleted in worksheet A.

Thanks
 
T

Tom

How would the indirect function work if the sumproduct function were
used? ie two rows of data are being worked with.

Thanks
 
H

Harlan Grove

RagDyeR wrote...
....
=SUM(INDEX(Sheet1!G:G,ROW(4:4)):INDEX(Sheet1!G:G,ROW(200:200)))
Click to expand...
....

You've exchanged susceptibility to row insertion/deletion in Sheet1 for
the same in the active sheet. Don't use ROW calls, just use

=SUM(INDEX(Sheet1!G:G,4):INDEX(Sheet1!G:G,200))
 
R

RagDyer

You're right ... never thought of using it that way.

The "self incrementing syndrome" is too prevalent and imbedded in my
thinking.

Thanks!
 
Top