Summation of Rows Problem

[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

 

[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

 

[David McRitchie]

Hi Tom,
You would have to put the G4:G200 within (double) quotes so that
it will not change.
=SUM(INDIRECT("G4:G200"))
=SUM(INDIRECT("Sheet_A!G4:G200"))

additional examples
http://www.mvps.org/dmcritchie/excel/indirect.htm

 

[Tom]

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

Thanks

 

[David McRitchie]

A quick web search: sumproduct indirect -hth

http://homepage.ntlworld.com/ken.wright/ExSumProduct2.html

http://www.mcgimpsey.com/excel/threedsumif.html

 

[Harlan Grove]

RagDyeR wrote...
....

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

....

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

 

[RagDyer]

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

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

Thanks!