Automatic answer checking in Google Forms using ArrayFormula

Other Questions Google Sheets
1

Hey everyone! I’m trying to set up an automatic grading system for a Google Form I made. The answers come in on a response sheet, and I want to check them against the correct answers on row 2.

I’ve got this working for individual columns using ArrayFormula like this:

=IF(ARRAYFORMULA(A3:A=A2),1,0)

It’s great, but now I’m stuck. I want to add up all the correct answers for each submission in a single column. I tried this:

=IF(ARRAYFORMULA(TRUE),SUM(D3:Z3),0)

But it just sums D3:Z3 for every row. I need it to sum the correct row for each new submission. I’ve tried tweaking it a bunch of ways, but no luck.

Any ideas on how to make this work? I’m scratching my head here! Thanks for any help you can give!

2

I’ve been using Google Forms for a while now, and I think I might have a solution for you. Have you considered using the ARRAYFORMULA in combination with SUMPRODUCT? Here’s what I’ve found works well:

=ARRAYFORMULA(SUMPRODUCT(D3:Z=D$2:Z$2))

This formula compares each cell in the range D3:Z to the corresponding cell in D2:Z2 (your correct answers), then sums up all the TRUE results (which are treated as 1).

The beauty of this approach is that it automatically adjusts for each new submission without needing to drag the formula down. It’s been a real time-saver for me when dealing with larger datasets.

Just make sure your correct answers in row 2 are in the exact same format as the submitted answers to avoid any mismatches. Hope this helps!

3

I’ve encountered a similar challenge with Google Forms. Instead of using SUMIF, you might want to consider the MMULT function combined with an equality check. Here’s a formula that should work:

=ARRAYFORMULA(MMULT(N(D3:Z=D$2:Z$2),TRANSPOSE(COLUMN(D3:Z)^0)))

This compares each response to the correct answers in row 2, then sums up the matches. The beauty is it’ll automatically update for new submissions. Just adjust the range (D3:Z) to match your actual data columns.

Remember to thoroughly test it with various scenarios to ensure accuracy. Good luck with your grading system!

4

hey Grace, i’ve dealt with this before! try using SUMIF instead. something like:

=ARRAYFORMULA(SUMIF(D3:Z=‘correct answer’,1,0))

replace ‘correct answer’ with the actual correct answer for each column. this should give u a total for each submission. lmk if it works!