'How to count values if option checked is selected in one cell

I have a spreadsheet where users fill out a form, they have multiple options they can pick by clicking the checkboxes.

The responses sheets looks like this

I want to filter the results by option, so my main goal is to get the total amount of options selected:

I've been trying with COUNTIF but it's not working because I'm not selecting the specific response

=COUNTIF('Form Responses 1'!C2:F4,"*"&$B$1&"*")

I'm assuming I need to add a VLOOKUP but I'm not sure how to match it with the option

Here's a spreadsheet to play around with it

Solution 1:^[1]

try:

=INDEX({QUERY(TRIM(SPLIT(FLATTEN(A2:A&"×"&SPLIT(B2:B, ",")), "×")), 
 "select Col1,count(Col1) where Col2 is not null group by Col1 pivot Col2 label Col1 'Person'"); 
 {"Total", TRANSPOSE(MMULT(TRANSPOSE(QUERY(QUERY(TRIM(SPLIT(FLATTEN(A2:A&"×"&SPLIT(B2:B, ",")), "×")), 
 "select count(Col1) where Col2 is not null group by Col1 pivot Col2"), "offset 1", )*1), 
 SEQUENCE(COUNTUNIQUE(TRIM(FLATTEN(SPLIT(TEXTJOIN(",", 1, B2:B), ",")))), 1, 1, )))}})

Solution 2:^[2]

Perhaps a formula like this:

=IFNA(QUERY({$A$2:$A$6, $B$2:$B$6}, "Select 1 where Col2 contains '"&B$9&"' and Col1 = '"&$A10&"' order by Col1 label 1 ''"), 0)

This outputs a 1 if the referenced cell contains the specified text, and a 0 otherwise. See the docs for the Query Function and the Query Language for more information.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	player0
Solution 2	2pichar

'How to count values if option checked is selected in one cell

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]