Reputation: 3
I am using Google Forms to create a survey with weighted answers. I've been able to make things work when there is just one possible correct answer - I made a separate tab with a set of answer tables with point values assigned, then used vlookup to call back and match the given response to the answer table and fetch the assigned point value.
=VLOOKUP(P2, Sheet2!$A$49:$B$50, 2, FALSE)
P2 is a value pulled from the "Form Responses" tab - in this case, a yes/no answer. Sheet 2 has a table for each question with the possible answers and the point values for each answer (A49=yes, A50=no)
However, for some of the questions, multiple answers are valid and I want to add up the total number of points for that given question. So for example: What are your hobbies? and folks can choose from
And the respective point values are 2, 2, 3, 4, 4
So then, if someone chose the "Swimming" and "Going fishing" checkboxes in the form, I'd get "7", and if someone chose "Riding your bike", "Playing football", and "Painting", I'd get "8".
I realize that the output from the Google form will list the chosen answers all in one cell (Playing football, Going fishing), so I'm not sure how to make it count each answer (especially since some of them are multi-word answers) and output the sum of the values.
Upvotes: 0
Views: 235
Reputation: 1
VLOOKUP is not suitable in this case. try FILTER like:
=FILTER(Sheet2!B49:B50, Sheet2!A49:A50=P2)
then VLOOKUP it like:
=SUMPRODUCT(IFNA(VLOOKUP(FILTER(Sheet2!B49:B50, Sheet2!A49:A50=P2), sheetx!A:B, 2, 0)))
where sheetx!A:B is like:
Riding your bike | 2 |
Playing football | 2 |
Swimming | 3 |
Going fishing | 4 |
Painting | 4 |
and if Sheet2!B49:B50 contains multiple comma+space separated values you will need to split them like:
=SUMPRODUCT(IFNA(VLOOKUP(FILTER(
IFERROR(SPLIT(Sheet2!B49:B50, ", ")), Sheet2!A49:A50=P2), sheetx!A:B, 2, 0)))
Upvotes: 2