Count the number of times a word from a list of words appears in a column

[martin newby]

Hi, I have been looking for an answer to this problem quite a while so any help would be greatly appreciated.
I have spread sheet (test calls) on which each row has a cell (resolve text) where text has been entered, I need to count the number of calls (rows) where a word from the "included" column appears but not if a word from the excluded column appears for each product, each row (call) should only be counted once. The actual spread sheet has 21000 rows and there are hundred plus words I need to include / exclude.
I have tried defining a name to use in the formula =COUNTIFS(B:B,Scanner) where scanner refers to =Data!$E$3:$E$10
also tried to define printer to refer to ={"*printer*","*spindle*"}.
The formula is only picking up on the first word in both instances.


thanks in advance
Martin

 

[Asheesh]

Hi Martin - First of welcome to the forum..

Not sure if I got you correct - check if this is what you were looking for


COUNT(SEARCH(Scanner,TRANSPOSE($B$2:$B$12)))-COUNT(SEARCH(scanner_ex,TRANSPOSE($B$2:$B$12)))

Acknowledge with CTRL + SHIFT + ENTER