How to count cells containing specific text, when cell includes list of text?

[sammybme]

I'm trying to count the number of decisions by application in a data set. What formula should I use? I tried a countifs but could not figure out how write out how to call out one application by name. I have a list of a hundred apps so doing this using a cell reference rather than typing "text" would be best.

My data set indicates decisions made in one cell e.g. "live, undecided, wish list, not implementing". There is only one decision listed in each cell. Applications impacted are listed in another cell. The cell that lists applications has multiple items listed, e.g. "apple, banana, orange, pear". I want a count by application of the decisions made.

Data set looks like this...

Enhancement	Decision	Application impacted
option 1	not implementing	apple, banana, pear
option 2...	live	pear, mango

<TBODY>
</TBODY>

I'm trying to fill in this table...

Application </SPAN>	Live</SPAN>	Wish List</SPAN>	Not Implementing</SPAN>	Undecided</SPAN>
Apple	?	?	</SPAN>?	?
Banana	?	?	</SPAN>?	?
Pear	?	?	</SPAN>?	?

<TBODY>
</TBODY><COLGROUP><COL><COL span=4></COLGROUP>

 

[AlanY]

A B C D E F G 1 Enhancement Decision Application impacted Apple Banana Pear Mango 2 option 1 not implementing apple, banana, pear 1 8 16 0 3 option 2 live pear, mango 0 0 1 7 4 option 3 Wish List pear, mango, pear 0 0 1 7 5 option 4 Undecided pear 0 0 1 0 6 7 8 Application live Wish List not implementing Undecided 9 Apple 0 0 1 0 10 Banana 0 0 1 0 11 Pear 1 1 1 1 12 Mango 1 1 0 0 <tbody> </tbody>

<tbody>
</tbody>

this solution with helpers in D1-G5,
in D2 =IFERROR(SEARCH(D$1,$C2),0) copy across and down to G5.

in B9 =SUMPRODUCT(--($B$2:$B$5=B$8),--($D$2:$D$5>0))
B10 =SUMPRODUCT(--($B$2:$B$5=B$8),--($E$2:$E$5>0))
B11 =SUMPRODUCT(--($B$2:$B$5=B$8),--($F$2:$F$5>0))
B12 =SUMPRODUCT(--($B$2:$B$5=B$8),--($G$2:$G$5>0))

and copy across