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


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


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!