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
• I have the same problem where if I add a row below the last row in the list, above the total row, the `SUM` range in the total row doesn't get updated. I often end up using a dummy row above the total which is included in the `SUM` range and therefore will always ensure any new rows added below the data row, above the dummy row, are included in the `SUM` range. – ADTC Mar 6 at 9:09

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
• Won't work if your `SUM` is also in column A. The easiest solution is to simply include the header row. Thus, `=SUM(A1:A50)`. The text in the header is ignored (if you need to put a number in header, make sure to use a single quote in front of it). A more complex solution is to enable iterative calculation and use `=SUBTOTAL(9, A:A)` – ADTC Mar 6 at 9:05

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
• Best educators understand the actual needs and educate based on that, and not simply answer the direct questions. – ADTC Mar 6 at 9:11