"LA Spellveny" wrote:
I have an excel doc that has a more involved SUM formula at
the bottom of a column. =SUM(Q55*I55)+(Q56*I56)+......

There is no good reason to use the SUM function in that context. Simply
write:

=Q55*I55 + Q56*I56 +...

Putting parentheses around the individual product terms is unnecessary; it
is a matter of personal preference. Some people prefer it for "clarity".
Ironically, I find the unneeded parentheses to be confusing in many cases.

But....


"LA Spellveny" wrote:
When I insert rows, this SUM formula obviously does not update to
account for the new rows. Sometimes, my colleagues will add up to 20 new
rows of data without realizing they aren't getting summed at the bottom.
Is there an easy way to ensure they are included in the SUM formula.

That depends on your worksheet design and how you "insert" and "delete"
rows.

For example, if there is no irrelevant data in subsequent rows, you might
write simply:

=SUMPRODUCT(Q55:Q100,I55:I100)

where Q100 and I100 are intended to be the maximum rows you will ever have
data. Use Q1000 or Q10000, if that makes sense.

Alternatively, I usually put an empty row before and after a table of data
to be sum(marized). That might be rows 55 and 100. Then the automatic
adjustment to such formulas always include all of the current data.


"LA Spellveny" wrote:
I've looked into the Offset option, but I'm not sure it would work
here since we have an equation in the formula and not just a range.

You certainly can use OFFSET or perhaps INDIRECT. Exactly how and how that
might benefit you again depends on your worksheet design.

But beware that some functions like OFFSET and INDIRECT are "volatile".
Thus, any formulas that use them and any dependent formulas are recalculated
every time __any__ cell in __any__ worksheet is edited.

That can result in significant slowdown of editing operations if you have a
lot of such formulas.

I try to avoid "volatile" formulas.

If you need further assistance, it would be prudent to post your exact
formulas. Better still, upload an example Excel file to a file-sharing
website and post the URL in a response here. The following is a list of
some free file-sharing websites; or use your own. (I believe
excelbanter.com allows you "attach" files to postings. They appear as URLs
to the rest of us.)

Box.Net: http://www.box.net/files
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha http://www.rapidshare.com
Windows Live Skydrive: http://skydrive.live.com