Formula to analyse multi-select responses in a restricted drop down list

Naina Dave

Hello,

I have built a Form for a survey where users can select multiple responses from a drop down list. How do I write a formula to count number of repeats for each response from the multi-select list?

For example: My restricted drop down list has - Car, Train, Airplane, Bike, Helicopter, Truck, Boat, RV. Users have to select up to 5 modes of transport that they like.

User 1 selected : Car, Train, Airplane, Bike, Bus

User 2 selected: Train, Bus, Bike

User 3 selected: Car, Train, Helicopter, Truck

User 4 selected: Airplane, Boat, RV

To analyze the responses, I would like to count total number of responses with Car, total number of responses with Train and so on.. so, in the above example, my answers would be:

Car - 2, Train - 3, Airplane - 2, Bike - 2, Bus - 2, Helicopter - 1, Truck - 1, Boat - 1, RV - 1

Or if there is another way to analyze the responses, I am open to suggestions.

Thank you

Naina

DKazatsky2

Hi @Naina Dave ,

Try changing Transport@row to @cell as shown in Eric's example.

Eric Law

Hello @Naina Dave

You can do a COUNTIF and CONTAINS for each data point. You can either have a roll up sheet that you put the data or use the summary tab to aggregate that data. Example for car: =COUNTIF(Response:Response, CONTAINS("Cars",@cell))

Naina Dave

Hi Eric,

Here's screenshot - is this what your meant?

Thank you

DKazatsky2

Hi @Naina Dave ,

Try changing Transport@row to @cell as shown in Eric's example.

Naina Dave

Thank You. It works now.