How do I automatically match text to numbers and sum those numbers in Google Sheets/ Forms?

I am using a google form to collect responses from students. For each student I would like to match 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…


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…


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.

Thanks for any help!

Go to Source
Author: jhaeff