Finding count of word in a range where some cells have multiple values?

[gravanoc]

What is the best formula to use to find an accurate count of a word when that word could possibly appear as part of another word?

Ex: Word to search for is "Excel" in range A3:A255. However, it should not count as an occurrence if "Excel" is found inside another phrase, like "Excellent" or "Excel Is Great". Each value is separated by a comma without a trailing or preceding space. Last, although this isn't a current scenario, if the word is repeated in a cell it would be interesting if that is taken into account, but is not necessary. My example table below should help clarify. If the formula met all but the last condition, the count would be 21. With the last condition it would be 25.

MASTER Showpad tools_Tag Reporting vFinalOnline.xlsm
	AW
3	Excel
4	Excel Is Great
5	Excel,Excel Is Great
6
7	Excel
8	Excel
9	Excel,Excel
10	Excelsior
11
12	Excel In Life
13
14	Excel
15
16
17	Excel
18	Excellent
19	Excelsior
20	Excelsior
21	Excel,Excel
22	Excelsior
23
24	Excelsior
25	Excellent
26	Excelsior
27	Excelsior
28	Excelsior
29	Excel,Excel
30	Excelsior
31
32	Excelsior
33	Excelsior
34
35
36
37	Excel
38	Excelsior
39	Excelsior
40
41
42	Excel Is Great
43	Excelsior
44
45	Excellent
46	Excel
47
48	Excel
49	Excelsior
50	Excelsior
51	Excelsior
52	Excelsior
53	Excelsior
54	Excellent
55	Excelsior,Excel
56	Excel In Life
57	Excelsior
58
59
60
61	Excelsior
62	Excelsior
63	Excel
64	Excelsior
65	Excelsior,Excel
66	Events
67	Excelsior
68
69
70
71	Excellent
72	Excellent
73	Excel Is Great
74
75
76	Excellent
77
78	Excellent
79
80
81
82	Excelsior
83
84	Excelsior,Excel
85	Excelsior
86	Excelsior,Excel
87
88
89	Excelsior
90	Excelsior
91
92	Excellent
93	Excellent
94	Excelsior
95	Excellent
96	Excelsior
97	Excelsior
98	Excelsior
99
100	Excelsior
101	Excellent
102
103	Excelsior
104	Excelsior
105	Excelsior
106	Excel In Life
107
108	Excel
109
110
111
112	Excellent
113	Excellent
114
115	Excel
116
117
118
119	Excelsior
120	Excellent
121	Excelsior
122
123	Excellent
124	Excellent
125	Excelsior
126
127	Excellent
128
129
130
131
132	Excel Is Great
133	Excellent
134	Excelsior
135
136	Excelsior
137	Excellent
138	Excel,Excel
139	Excellent
140
141	Excellent
142	Excellent
143	Excelsior
144	Excellent
145
146	Events
147	Excellent
148
149
150	Excellent
151
152
153
154	Excellent
155	Excellent
156	Excelsior
157
158	Excellent
159	Excellent
160	Excel Is Great
161
162	Excellent
163	Excellent
164	Excellent
165	Excellent
166	Excelsior
167
168	Excellent
169	Excellent
170
171	Excellent
172	Excellent
173	Excellent
174	Excelsior
175	Excellent
176	Excelsior
177
178	Excellent
179
180	Excellent
181	Excelsior
182	Excellent
183
184	Excellent
185	Excellent
186	Excelsior
187	Excelsior
188
189	Excellent
190	Excellent
191	Excellent
192	Excellent
193	Excellent
194
195	Excellent
196	Excellent
197	Excel
198	Excellent
199	Excellent
200	Excellent
201	Excellent
202	Excellent
203	Excellent
204	Excellent
205	Excellent
206	Excellent
207	Excel Is Great
208	Excellent
209
210
211
212	Excellent
213	Excellent
214
215	Excellent
216
217	Excellent
218	Excellent
219	Excelsior
220
221	Excellent
222	Excellent
223	Excellent
224
225	Excellent
226	Excellent
227
228	Excellent
229
230
231
232	Excelsior
233
234	Excellent
235	Excelsior
236	Excellent
237	Excellent
238
239	Excel Is Great
240	Excellent
241	Excellent
242	Excellent
243	Excellent
244	Excellent
245
246
247
248
249	Excelsior
250
251
252
253
254
255
256
257
Data

 

[Habtest]

Please try:
Condition 1:

=COUNTA(FILTER(AW3:AW255,FIND("Excel,",AW3:AW255&",")>0))

Condition 2:

=LEN(TEXTJOIN(",",1,AW3:AW255)&",")-LEN(SUBSTITUTE(TEXTJOIN(",",1,AW3:AW255)&",","Excel,","Excel"))

 

[gravanoc]

Please try:
Condition 1:

=COUNTA(FILTER(AW3:AW255,FIND("Excel,",AW3:AW255&",")>0))

Condition 2:

=LEN(TEXTJOIN(",",1,AW3:AW255)&",")-LEN(SUBSTITUTE(TEXTJOIN(",",1,AW3:AW255)&",","Excel,","Excel"))

Thanks, solution #2 worked.

 

[Peter_SSs]

Thanks, solution #2 worked.

Are you sure?
You said ..

it should not count as an occurrence if "Excel" is found inside another phrase

My understanding then is that the small sample below should result in a count of 6, but that formula counts 8 (including "FastExcel" & "Pre-Excel") as shown by the cell AY3 formula.
So, I guess it depends on whether "excel" is your actual or only word to check and whether the word(s) being checked may also come at the end or in the middle of other words or only at the beginning.
In any case, to be surer of only counting the exact word I would suggest the cell AX3 formula.

22 09 06.xlsm
	AW	AX	AY
3	Excel	6	8
4	Excel Is Great,Excel
5	Excel,Excel Is Great
6
7	FastExcel,Pre-Excel
8	Excel
9	Excel,Excel
Count

Cell Formulas
Range	Formula
AX3	AX3	=LET(t,SUBSTITUTE(","&TEXTJOIN(",",1,AW3:AW9)&",",",",",,"),(LEN(t)-LEN(SUBSTITUTE(t,",Excel,","")))/7)
AY3	AY3	=LEN(TEXTJOIN(",",1,AW3:AW9)&",")-LEN(SUBSTITUTE(TEXTJOIN(",",1,AW3:AW9)&",","Excel,","Excel"))