Counting values embedded in strings inside a column (Google Spreadsheets)

Question

I have a Google Survey where I created some multiple choice questions. Now, I am trying to count the responses.

[A]          [B]
[Response#]  [Selections]
[1]          [Apple,Orange,Banana]
[2]          [Orange,Banana]
[3]          [Apple,Orange,Banana]
[4]          [Banana]
[5]          [Apple,Banana]
[6]          [Apple,Orange]
.

So on my summary spreadsheet, I would like to have the totals:

[Favorite Fruit] [Total]
[Apple]          [4]
[Orange]         [4]
[Banana]         [5]
.

I tried using:

 =countif('Responses'!B:B,find(A2)) 

but got no results (where A2 was the word "Apple"). I know that find is incomplete, then I also tried putting find(A2,'Responses'!B:B) but that's not right. I'm stumped.

Answer 1

You would have to use wildcards.. please try:

=countif('Responses'!B:B,"*"&A2&"*")

and see if that works ?

Answer 2

Assuming Apple, Orange and Banana are in cells A1, A2 and A3 of your summary sheet respectively and that your data is in a seperate sheet called 'Data' in cells A1:B20, you can use the following:

=SUMPRODUCT((NOT(ISERROR(FIND(A1, Data!$A$1:$B$20)))*1))