1

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?

Improve this question
4
  • 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
    Commented Jan 18, 2017 at 18:02
  • Well that header is need to tell me what information is in what column.
    – ben
    Commented 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.
    – Karl_S
    Commented 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.
    – ADTC
    Commented Mar 6, 2020 at 9:09

2 Answers 2

Reset to default
2

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.

Improve this answer
2
  • 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
    Commented Jan 18, 2017 at 20:03
  • 1
    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
    Commented Mar 6, 2020 at 9:05
0

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.

Improve this answer
4
  • 1
    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
    Commented 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
    Commented 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.
    – BrettFromLA
    Commented Jan 19, 2017 at 16:20
  • 1
    Best educators understand the actual needs and educate based on that, and not simply answer the direct questions.
    – ADTC
    Commented Mar 6, 2020 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.