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? Jan 18, 2017 at 18:02
• Well that header is need to tell me what information is in what column.
– ben
Jan 18, 2017 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. Jan 18, 2017 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.
Mar 6, 2020 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. Jan 18, 2017 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)`
Mar 6, 2020 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"? Jan 18, 2017 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, 2017 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. Jan 19, 2017 at 16:20
• Best educators understand the actual needs and educate based on that, and not simply answer the direct questions.