I'm using an online third-party form to collect data, that data gets submitted to Google Sheets. I applied a sum formula to calculate the sum of a column `=SUM(A2:A50)`. `A1` is used for header. Now, when the data comes in into Google Sheets, a new row gets created, and Google Sheets updates my formula automatically to only calculate rows `=SUM(A3:A51)` skipping row `A2`. Is there anyway to prevent Google Sheets from meddling with my formula?

• Is the newly entered data placed in row 2? If so, is there any way to change this in the third party form? – Karl_S Jan 18 '17 at 18:02
• Well that header is need to tell me what information is in what column. – ben Jan 18 '17 at 18:56
• That is row 1, correct? It sounds from your description that the form is adding a row "above" the one where your formula is located. I thought this would have been in row 2 based on your formula. – Karl_S Jan 18 '17 at 19:02

Basically I fixed the problem by simply doing a generic sum formula =SUM(A:A). This way no matter if new rows are created or subtracted the formula stays the same.

• In the other answer, I'd added a comment about the bottom row changing when a row is added at the top. This solution of yours solves that problem. – BrettFromLA Jan 18 '17 at 20:03

You can prevent any changes to a range by making it a string within `indirect`:

``````=sum(indirect("A2:A50"))
``````

Here, "A2:A50" is just a string argument, which never mutates into anything else.

• When the new row is added, does should the BOTTOM cell in the range be changed? For example, should it change from "A2:A50" to "A2:A51"? – BrettFromLA Jan 18 '17 at 20:02
• @BrettFromLA The OP asked how "to prevent Google Sheets from meddling with my formula" and this is what this answer provides. It's quite possible that what they asked for is not what they actually needed, but I answer the question that was asked. – user135384 Jan 18 '17 at 20:04
• @zaq You're exactly right! It's up to the OP to realize that the formula NEEDS to be meddled with a little, to accommodate the bigger range. – BrettFromLA Jan 19 '17 at 16:20