I have created a form using google forms, and I want to automatically check the answers when a new submit is done. When the user submit his/her question, a new line is added in a responses sheet. This is where I need to add some magic ;)
SO, in my responses sheet, I have the right answers in the second line...something like this
A B C
1
2 RightAnswer1, RightAnswer2, RightAnswer3....
Using the arrayformula function, I have added new colums (column D,E,F,G and so on) on every row checking against the correct answer on line 2.
The formula goes something like this;
=IF(ARRAYFORMULA(A3:A=A2),1,0)
And it works like a charm :)
BUT, then I want to add a column at the end of each row with the sum of all the right answers, lets say to column Z on every row...this is where I want to sum all the 1's (correct answers).
I have tried something like this;
=IF(ARRAYFORMULA(<any kind of check that is true>),sum(D3:Z3),0)
This will always return the sum of D3:Z3 on every line, and this is not what I want. I want to sum the columns on the line-number that was just added. So I have tried to alter this expression in several ways , having everything in the arrayformula expression, altering the sum expression to (D3:D) to hopefully get it to work on the current line, but without any luck.
I have search the web for answers, but found nothing to help me... Do you have any suggestions?
