Search code examples
excelpivot-tablemultiple-choice

How to process multiple choice answers from google forms in excel to pivot table

I'm processing a bunch of results from a google forms result spreadsheet into an excel spreadsheet (making nice graphs and charts) but I cannot figure out how to process the multiple choice question answers in the way google provides them.

In the image below you see some example results. The rows represent one person who filled in the form. The column name is the question itself and in the cells you'll see the multiple answers a person selected (checkboxes).

response table

Now I would like to get an pivot table with the total percentage of times an answer is chosen.

I also made a small table with all the answer possibilities (see image below) but still can't figure out how to get the total number or percentage off occurrences of those answers in the other table.

all answers table

Can someone tell me how to make this pivot table in a fast en efficient way, i have a lot more of these type of answers and questions to process so i can't go and type every possible answer in a table.

So this would be the desired result:

desired result

Thanks in advance!

Solution

  • Found the answer here: Formula in Excel to count occurrences of substrings within strings

    Basically, you need to search for a substring within a list of strings. This is the equation to use.

    =COUNTIF(D2:D7;"*"&<answers>&"*")