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

gravanoc

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

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Please try:
Condition 1:
Excel Formula:
=COUNTA(FILTER(AW3:AW255,FIND("Excel,",AW3:AW255&",")>0))
Condition 2:
Excel Formula:
=LEN(TEXTJOIN(",",1,AW3:AW255)&",")-LEN(SUBSTITUTE(TEXTJOIN(",",1,AW3:AW255)&",","Excel,","Excel"))
 
Upvote 0
Solution
Please try:
Condition 1:
Excel Formula:
=COUNTA(FILTER(AW3:AW255,FIND("Excel,",AW3:AW255&",")>0))
Condition 2:
Excel Formula:
=LEN(TEXTJOIN(",",1,AW3:AW255)&",")-LEN(SUBSTITUTE(TEXTJOIN(",",1,AW3:AW255)&",","Excel,","Excel"))
Thanks, solution #2 worked.
 
Upvote 0
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
AWAXAY
3Excel68
4Excel Is Great,Excel
5Excel,Excel Is Great
6
7FastExcel,Pre-Excel
8Excel
9Excel,Excel
Count
Cell Formulas
RangeFormula
AX3AX3=LET(t,SUBSTITUTE(","&TEXTJOIN(",",1,AW3:AW9)&",",",",",,"),(LEN(t)-LEN(SUBSTITUTE(t,",Excel,","")))/7)
AY3AY3=LEN(TEXTJOIN(",",1,AW3:AW9)&",")-LEN(SUBSTITUTE(TEXTJOIN(",",1,AW3:AW9)&",","Excel,","Excel"))
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top