Google Sheets: Sum Row Without Overwriting Formula When Google Forms Adds New Row

google sheetsgoogle-forms

I'm using Google Forms to collect some data from students. Each time a new student submits a form, the data (4 columns worth) is added to a new row in a Google Spreadsheets, bumping anything previously in that row. It will also replace any formulas I have written in columns outside of the data range (they'll be left blank).

Note: because I am using certain features (trigger script upon submission) of the AutoCrat script I have to do all this in the old versions of Google Forms & Spreadsheets…

I have been able to "score" each results by adding an answer key as the first form submission in row 2 and then comparing incoming answers to it with an ArrayFormula in columns G to J (e.g., =ARRAYFORMULA(IF(C3:C=C$2,1,0)) and so one for each column to be scored).

However, I've run into a problem, since I can't seem to write an ARRAYFORMULA to place in column K that simply sums the row from columns G-J. I've tried lots of ways, but I'm sure the syntax is just off. Does anyone know a way?

I've read this, and this, and this, but they haven't helped.

Here is an MWE of the Form and Spreadsheet

Data goes into columns A-F, the data in C-F is scored with ArrayFormulas in G-J and a flubbed attempt at summing the scores is in column K.
Feel feel to edit the file.

Best Answer

Please try: 

=ArrayFormula(G2:G+H2:H+I2:I+J2:J)
Related Topic