Counting key words in cells

[beginvbaanalyst]

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.

 

[jasonb75]

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

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