Hi all,
I am trying to extract unique values from a column that has multiple data (semicolon separated) in each cell. This data is from a Google form that I downloaded, each value representing a checkbox value (from the form). What I'm trying to do is for each value, find the count, i.e. number of times that value was checked in the form. I can see a percentage distribution from the Google form 'Responses' tab, but am unable to replicate the same on excel.

Column:

ACTIVITY_ALL
Motorcycling;WaterS;S/R;Rock Climbing
Motorcycling;Mountaineering;Rock Climbing
None
Mountaineering
WaterS;S/R
WaterS;Sky
None
Mountaineering;Rock Climbing;Scuba Diving
Motorcycling;Mountaineering;WaterS;S/R;Rock Climbing
Mountaineering;WaterS;Surfing;Rock Climbing;Scuba Diving;Sky
Motorcycling;Mountaineering;WaterS;S/R;Rock Climbing;Sky
WaterS;Sky
Motorcycling;WaterS;S/R;Rock Climbing;Skiing
Mountaineering
Rock Climbing
S/R
Motorcycling;Mountaineering;WaterS;Surfing;Rock Climbing
None
Motorcycling;Mountaineering;WaterS;Surfing;Rock Climbing
WaterS;Sky
Motorcycling;Mountaineering;Rock Climbing
WaterS
Mountaineering
WaterS;Rock Climbing
Motorcycling;WaterS;S/R;Rock Climbing
Motorcycling;Rock Climbing
Mountaineering;S/R;Rock Climbing
WaterS;S/R;Rock Climbing
Mountaineering;S/R;Rock Climbing;Obstacle Course
Motorcycling
Motorcycling;Mountaineering;WaterS;Surfing
Motorcycling;Mountaineering
Mountaineering
Motorcycling;WaterS;Surfing;S/R;Scuba Diving;Sky
Mountaineering
WaterS
Mountaineering;WaterS;Surfing;S/R;Rock Climbing
Motorcycling
S/R;Rock Climbing
Mountaineering;WaterS;Surfing;Rock Climbing
Motorcycling
S/R;Rock Climbing;Sky
Motorcycling;Mountaineering;WaterS
WaterS
Motorcycling
Motorcycling;Mountaineering;WaterS;S/R;Scuba Diving;Sky
Mountaineering;WaterS;Rock Climbing;Sky
Motorcycling;WaterS;Sky
None
S/R
S/R
Trekking
S/R
Motorcycling
Motorcycling;WaterS;S/R;Sky
None
Nature Tour
Motorcycling
Mountaineering;WaterS
Scuba Diving
Motorcycling
Motorcycling;Mountaineering
Motorcycling;Mountaineering;WaterS;Rock Climbing
Motorcycling;Mountaineering;Surfing;Scuba Diving
Mountaineering;Rock Climbing;Scuba Diving
Mountaineering;WaterS;Rock Climbing
Mountaineering;Rock Climbing
Mountaineering;WaterS;Rock Climbing;Sky
Motorcycling;WaterS;Scuba Diving
WaterS;Rock Climbing;Sky
Mountaineering;Rock Climbing
Surfing;Rock Climbing;Scuba Diving
Motorcycling;WaterS;Scuba Diving
Mountaineering;WaterS;Rock Climbing
WaterS;Rock Climbing
Mountaineering;WaterS;Scuba Diving
Motorcycling;Mountaineering;Rock Climbing
Mountaineering;Rock Climbing
WaterS;Rock Climbing
WaterS;Rock Climbing
Mountaineering;WaterS;Rock Climbing
Motorcycling;Mountaineering;WaterS;Scuba Diving
WaterS;Rock Climbing
Rock Climbing
Motorcycling
WaterS;Rock Climbing
Mountaineering;WaterS;S/R
WaterS;Scuba Diving
S/R;Rock Climbing
Mountaineering;WaterS;Rock Climbing
WaterS;Sky
Mountaineering;WaterS
Mountaineering;WaterS;Rock Climbing;Sky
WaterS;S/R;Sky
WaterS
Motorcycling;Mountaineering;WaterS;S/R;Rock Climbing;Scuba Divin
Motorcycling;WaterS;S/R;Sky
WaterS;Surfing;S/R
WaterS;Scuba Diving;Sky
Motorcycling;Mountaineering;Surfing;S/R;Sky
S/R;Rock Climbing
Motorcycling;Mountaineering;WaterS;S/R;Rock Climbing;Sky
WaterS;S/R
WaterS;S/R;Rock Climbing;Sky
WaterS;S/R;Rock Climbing;Sky
Motorcycling;Mountaineering;S/R;Rock Climbing
Motorcycling;WaterS;S/R;Scuba Diving
Motorcycling;WaterS;Surfing;Scuba Diving
WaterS;S/R;Rock Climbing;Scuba Diving
S/R;Rock Climbing;Scuba Diving
Motorcycling;WaterS;S/R;Rock Climbing
Motorcycling;S/R;Rock Climbing
WaterS;Rock Climbing;Sky
WaterS;S/R
Mountaineering;WaterS
WaterS;S/R
Motorcycling;Mountaineering;WaterS;Sky
Motorcycling;Mountaineering
Motorcycling;Mountaineering;Rock Climbing
Motorcycling;Mountaineering;WaterS;S/R;Rock Climbing;Sky
Motorcycling;Mountaineering;WaterS
WaterS
Motorcycling;Mountaineering;S/R
Motorcycling;Mountaineering;WaterS
Mountaineering;WaterS
Motorcycling;WaterS;Surfing;S/R
Mountaineering;WaterS;Rock Climbing
Motorcycling;WaterS
Motorcycling;Mountaineering
Motorcycling;Mountaineering
Motorcycling;Mountaineering;S/R;desert trek
Mountaineering;WaterS
Motorcycling;Mountaineering;WaterS;Rock Climbing
Mountaineering;Rock Climbing
Motorcycling;WaterS
Motorcycling;Rock Climbing;Sky
Motorcycling;Mountaineering;WaterS
Motorcycling;WaterS;Rock Climbing
WaterS
Motorcycling;Mountaineering;WaterS;S/R;Sky
Motorcycling;Mountaineering;S/R
WaterS;Rock Climbing
Motorcycling;WaterS;Sky
WaterS
WaterS
Mountaineering;WaterS;Scuba Diving
Motorcycling;Mountaineering;WaterS;S/R;Rock Climbing;Sky
Motorcycling;Rock Climbing
WaterS;S/R;Rock Climbing
Motorcycling;WaterS;Surfing;S/R
Motorcycling;Rock Climbing
Motorcycling;Rock Climbing
Motorcycling;Mountaineering
Motorcycling;WaterS
Motorcycling
Motorcycling;Mountaineering;WaterS
Motorcycling
None
Motorcycling;S/R;Desert Trek
Motorcycling
Mountaineering;Rock Climbing
Surfing;S/R
Motorcycling;safari
Motorcycling;WaterS;S/R
Motorcycling
Motorcycling;Rock Climbing;Scuba Diving
Mountaineering;S/R;Scuba Diving
Motorcycling;Mountaineering;Scuba Diving;Sky
Mountaineering;WaterS;Sky
Motorcycling;S/R
Motorcycling
Motorcycling;Rock Climbing
Motorcycling;Sky
Motorcycling;Scuba Diving
Motorcycling;S/R
Motorcycling;Mountaineering
Motorcycling;Scuba Diving
Motorcycling;Surfing
Motorcycling;Rock Climbing
Motorcycling;Sky
Motorcycling;WaterS
Motorcycling
Motorcycling;Scuba Diving
Motorcycling;S/R
Motorcycling
WaterS;Rock Climbing;Scuba Diving
Rock Climbing
Motorcycling;Mountaineering;S/R
Motorcycling
S/R
Motorcycling;Rock Climbing
Mountaineering;Rock Climbing
Motorcycling;Rock Climbing
Motorcycling
Motorcycling;WaterS
None
Mountaineering;Rock Climbing
Motorcycling;WaterS
Motorcycling;Jungle Safari
Motorcycling;WaterS
Motorcycling;Rock Climbing
None
WaterS
Motorcycling;Surfing
Motorcycling;WaterS;Scuba Diving
Motorcycling
Motorcycling;Mountaineering;WaterS
Motorcycling;Mountaineering;WaterS;Sky
Motorcycling;Mountaineering;WaterS;S/R;Rock Climbing
WaterS
Motorcycling;Mountaineering;WaterS;Rock Climbing




Intended Result:
Motorcycling 111
WaterS 106
Rock Climbing 80
Mountaineering 80
S/R 58
Sky 34
Scuba Diving 27
Surfing 17
None 8
Desert Trek 2
Trekking 1
Nature Tour 1
Jungle Safari 1
Skiing 1


For the count, I have used the formula:
=COUNTIFS($A$2:$A$212,"*Motorcycling*") and so on. But the activity names I have written manually.

Pl help
Thanks