# Counting key words in cells

#### beginvbaanalyst

##### Board Regular
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.
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
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.

Replies
4
Views
333
Replies
1
Views
208
Replies
18
Views
370
Replies
6
Views
913
Replies
1
Views
159

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.

### Which adblocker are you using?

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

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