How to shorten the formula - IF(SUM(COUNTIF(

**fang603** · 03-09-2012, 08:52 AM

Hi,

Formula : =IF(SUM(COUNTIF(B2,{"*VACCIN*","*IBACK*","*BUSINESS*","*ICAEW*","*RECRUIT*"}))>0,"NT","T")

I have created a formula to detect certain words contain in a sentence. If the sentence contains the word in my list, the result is "NT", otherwise the result is "T".

I would like to seek for help to shorten the formula as the word I am looking for can be a lot (I just put a few examples of word in the above formula). The formula has to provide the flexibility to detect word like "vaccinations".

I have enclose the excel file for your reference.

Thanks.

**daddylonglegs** · 03-09-2012, 08:56 AM

Try using a reference to the list in the formula, e.g. as you have the words listed in A16:A20

=IF(SUMPRODUCT(COUNTIF(B2,"*"&A$16:A$20&"*"))>0,"NT","T")