Google-sheets – Adding row to Google Sheets updates SUM formula – I don’t want this

formulasgoogle sheets

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?

Best Answer

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.

Related Solutions

Google-sheets – Google Spreadsheet: Adding formulas to Google Forms responses rows

If you add the following formula in cell D2:

=ARRAYFORMULA(FILTER(C2:C;C2:C<>"")*1.2)

then it will take on the complete column, via the array. The FILTER function, is to make sure no empty cells are taken into account.

I've added the result into the file you shared with us.

Google-sheets – Google Form keeps changing Spreadsheet cell entry

The problem was due to Google Forms adding a new row every time.

If you are having the same problem here's what you need to do to fix it:

Where you previously had your formula change it to:

=arrayformula('YOUR SHEET NAME'!A2:A)

Change the sheet name, and enter the cell you want to target, as-well as the column.

Make sure you delete all other formulas in that column.

This will automatically post the new data from the cell into the one below it. Just make sure you have no other formulas in the column. Also make sure the cell you target is the one where the form data is stored.

Best Answer

Related Solutions

Google-sheets – Google Spreadsheet: Adding formulas to Google Forms responses rows

Google-sheets – Google Form keeps changing Spreadsheet cell entry

Related Topic