Counting key words in cells

beginvbaanalyst

Board Regular
Joined
Jan 28, 2020
Messages
116
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone,

I'm currently taking descriptions and counting key words; but I'm afraid that if one or more key words are in the same cell, the formula will count each key word instead of if one meets the criteria and counts it as 1.
What's a formula around this to cancel out this issue?
Currently my formula is =SUMPRODUCT((LEN(C2:C1000)-LEN(SUBSTITUTE((UPPER(C2:C1000)),UPPER(L2),"")))/LEN(L2))
My L2 represents New Club, 364, and 372.
My first cell has the current text Article Change Request:-New Club Beer & LP Wine Endcap Submission - 364 & 372.
How do I counter this?
It would essentially leave a count of 3 instead of 1.
Please help.
Thank you.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,914
Office Version
  1. 365
Platform
  1. Windows
It is not clear what you're trying to do with that. L2 can only contain 1 of those things, if it contains all 3 then the first row will not be counted at all as it will be evaluated as a single string, not 3 separate ones.

Perhaps
Excel Formula:
=SUM(COUNTIF(C2:C200,"*"&L2:L4&"*"))
where L2:L4 each contain 1 of the 3 key words. Note that if you are not using 365 as shown on your profile then you will need to array confirm the formula with Ctrl Shift Enter.

If this doesn't do what you need then try posting a small example with the expected counts using XL2BB (link in my signature block below) so that we can see clearly what is required and use the sample data for testing.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,701
Messages
5,654,815
Members
418,155
Latest member
demasisi

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
Top