0

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 Example using vlookup for each row

1
0

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#1


Example#2

enter image description here

2
  • Works beautifully thank you! – jhaeff Aug 26 '20 at 15:53
  • I should have added that more competent users than me might have developed a more efficient solution - that is, there is bound to be more than one solution. OTOH, if it is useful then you might consider "accepting" the answer. – Tedinoz Aug 26 '20 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.