I am using a google form to collect responses from students. For each student I would like to match those text responses to specific numbers. Never = 0, Almost Never = 1, etc. Then sum the results for each student. So if a student answered five questions with "Almost Never" the end result would be 5. Responses are fixed allowing for a vlookup or match function.
Since it's a google form, I've been using an ArrayFormula to sum each student's responses. Something like this...
=ArrayFormula(G2:G+H2:H+I2:I+J2:J)
However this requires me to have a separate vlookup for each column to match the text to number. If I manually enter vlookup for each row, I can neatly combine vlookup and sum in one formula such as this...
=ArrayFormula(sum(vlookup([range],[vlookup table],2,False)))
However this does not work for any future students. Is there a way to nest these formulas? Or another option? Ideally I would like to have one formula that for each row automatically matches fixed text responses to numbers, sums those numbers and can accommodate future rows/students responding.