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.

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.