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

Question

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.

Example

Answer 1

You want to sum the values for several columns on each row of a Google Form Response. You are also concerned that, if more data is added, the formula should pick up those additional student results.

Use formula to add the values. Insert in cell G1.

=QUERY({ARRAYFORMULA(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(B2:F),,2^99))),"Almost Never","Almostever")," ",","))},"Almostever",2),"Never",1),"Sometimes",3),"Always",4),","))},"select Col1+Col2+Col3+Col4+Col5 label Col1+Col2+Col3+Col4+Col5 'Total'")

Note that the data range ("B2:F") is open ended, so as more responses are received, those results will be automatically included in the query (refer Example#1 vs Example#2). This query could be placed on another sheet; you could also sort results without affecting the undering data.

The sequence of logic in this answer is:

• {ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(B2:F),,2^99))),"Almost Never","Almostever")," ",","))}: this takes the five values in the row and creates a comma separated string.
  • note that "Never" is a partial match with "Almost Never", so in addition to combining the values, "Almost Never" is reduced to "Almostever" to avoid substitution errors at a later date.
  • the outcome looks something like this: "Almostever,Almostever,Always,Sometimes,Never"
• SUBSTITUTE(substitute(substitute(substitute(<<insert previous snippet>>,"Almostever",2),"Never",1),"Sometimes",3),"Always",4): this takes the comma separated string, and uses the substitute function to assign numbers to the various text responses.
  • the outcome looks something like this: "2,2,4,3,1"
• =split(<<insert previous snippet>>,","): this simply 'splits' the string into 5 values (one per column) using a comma as the delimiter.
• QUERY(<>,"select Col1+Col2+Col3+Col4+Col5 label Col1+Col2+Col3+Col4+Col5 'Total'")`: adds the values that relate to columns B to F.

---

Example#1

---

Example#2