Count instances of text in a comma-delimited column

Hello,

I have a Google Form that has created a spreadsheet containing the form's collected data. One column contains individuals' responses to a "check-box" style question. Each cell in this column contains a comma-delimited list of all of the check-boxes that were selected on an individual's form. For instance:

Cell C1: "first option, third option, fourth option, seventh option"
Cell C2: "first option, second option, seventh option"
...and so on.

How do I count the number of times "seventh option" appears in this column of responses? My first thought was to try =COUNTIF(FIND("seventh option",C:C),TRUE) ...but apparently FIND and COUNTIF don't play well together.

(And yes, I'm aware that Google provides exactly this kind of summary for its forms. But I'd like to mine more data, cross-reference different combinations of responses, etc.)

Thank you!
--David

Originally Posted by benishiryo

hi David, welcome to the forum. try:
=SUMPRODUCT(--(LEN(C1:C10)-LEN(SUBSTITUTE(C1:C10,"seventh option",""))))/LEN("seventh option")

use a bigger range if you need, but avoid using the whole column like C:C

Hi benishiryo, that seems to work really well. Thanks! SUMPRODUCT strikes again...I've seen it before, and I should have guessed that it would be helpful here.

Originally Posted by Harribone

See attached one possible way of doing this.

This is basically what you've tried to do broken into stages so you can see visually what choices were made.

Attachment 217670

Thanks Harribone...this works too, but I was hopeful to do it all in one step as given above. I appreciate your help!

--David