Responses are automatically added to the sheet Form Responses. I want to apply certain formulas in a sheet called Student Answers. However, Student Answers doesn't auto-update from the submitted responses. Here are the formulas I am trying to use:

='Form Responses'!A1` etc.
=IF('Form Responses'!E6='Answers'!$E$2,1,0)

Answers is another sheet with an answer key.


this sums the numbers given in the previous formula.

Basically, I have created a quiz, and I want to grade the quiz on a separate sheet.

Here is the Sheets in question.

1 Answer 1


The solution was to use =ARRAYFORMULA. Here are my working examples:

  • =ArrayFormula('Student Submissions'!A:C) for basic copying of columns

  • =ARRAYFORMULA(If('Student Submissions'!D3:D='Student Submissions'!D3,1,0)) for IF statements

  • =ArrayFormula(mmult(D3:I;TRANSPOSE(sign(column(D3:I))))) for SUM

  • =ARRAYFORMULA(J3:J/6*100) for AVERAGE and percent

Note: I found these answers in the Google Product forums but cannot find the link again.

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.