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...

[TABLE="width: 704"]
<TBODY>[TR]
[TD]Enhancement[/TD]
[TD]Decision[/TD]
[TD]Application impacted[/TD]
[/TR]
[TR]
[TD]option 1[/TD]
[TD]not implementing[/TD]
[TD]apple, banana, pear[/TD]
[/TR]
[TR]
[TD]option 2...[/TD]
[TD]live[/TD]
[TD]pear, mango[/TD]
[/TR]
</TBODY>[/TABLE]



I'm trying to fill in this table...
[TABLE="width: 704"]
<TBODY>[TR]
[TD]Application </SPAN>[/TD]
[TD]Live</SPAN>[/TD]
[TD]Wish List</SPAN>[/TD]
[TD]Not Implementing</SPAN>[/TD]
[TD]Undecided</SPAN>[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]? [/TD]
[TD]? [/TD]
[TD]</SPAN>?[/TD]
[TD] ? [/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]? [/TD]
[TD]? [/TD]
[TD]</SPAN>?[/TD]
[TD] ? [/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]? [/TD]
[TD]? [/TD]
[TD]</SPAN>?[/TD]
[TD] ? [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=4></COLGROUP>[/TABLE]

 

[AlanY]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]

	A	B
Enhancement	Decision	Application impacted	Apple	Banana	Pear	Mango
option 1	not implementing	apple, banana, pear
option 2	live	pear, mango
option 3	Wish List	pear, mango, pear
option 4	Undecided	pear
Application	live	Wish List	not implementing	Undecided
Apple
Banana
Pear
Mango

<tbody>
[TD="width: 141"]C[/TD]
[TD="width: 121"]D[/TD]
[TD="width: 74"]E[/TD]
[TD="width: 35"]F[/TD]
[TD="width: 49"]G[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]12[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

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