Picture column A full of comments like feedback from a survey question. Now picture column B with a formula which counts occurrances of particular key words within each comment. I am currently using this formula in column B: SUM(COUNTIF(A27,{"*LBNL*","*Lawrence Berkeley*","*LBL*","Lawrence Lab*"}))

Since the list will be growing and shrinking along the way and because I am going to end up with multiple similar formulas (for different categories) I would like to instead control the list in a named range and reference it from there.

So now let's say my list is like below and has name range of search_items1

- LBNL
- Lawrence Berkeley
- LBL
- Lawrence Lab

My formula would then look like SUM(COUNTIF(A27,search_items1)).

Notice the use of * for wildcard which introduces another challenge but I can't get the above formula to work even without the *. Is there a way to make this work? The solution with the wildcard * would be ideal.

Alternatively could I reference one cell that is concatenated together from the name range and would look like this: {"*LBNL*","*Lawrence Berkeley*","*LBL*","Lawrence Lab*"}. I attempted this but the formula interprets it as one text block.

I have tried multiple syntax variations and countless Google and Super User searches. Please help.

`SUMPRODUCT`

instead of`SUM`

). The namedrange can contain the asterisk and will be considered as wildcard. – Jerry Sep 9 '13 at 18:04`LBNL`

(and nothing else in the same cell), having`Lawrence Berkeley`

, etc and for the 4th text, any cell beginning with`Lawrence Lab`

. What you describe now changes everything since you nowmustput the wildcards to get the result you expect. Put asterisks before and after the text to be counted. And you don't need to use CSE with`SUMPRODUCT`

, that's why I suggested it over`SUM`

which you do need. – Jerry Sep 9 '13 at 18:26