Excel – way to use a name range as an array criteria for a sum(countif()) formula in Excel 2010

arraymicrosoft excelmicrosoft-excel-2010search

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.

Best Answer

What your formula does:

=SUM(COUNTIF(A27,{"LBNL","Lawrence Berkeley","LBL","Lawrence Lab*"}))

is count 1 if the cell A27 is LBNL, or Lawrence Berkeley or LBL or Lawrence Lab* (the * acting as a wildcard here).

So, if you have a cell being LBNL, you'll get 1 as result. Having Dr. LBNL will result in 0.

If you want to get a count of 1 if the cell contains at least 1 LBNL, then you need to use wildcards on both sides of the text, namely:

=SUM(COUNTIF(A27,{"*LBNL*","*Lawrence Berkeley*","*LBL*","*Lawrence Lab*"}))

You can make a named range and put it there instead, but this will become an array formula which will only work with Ctrl+Shift+Enter:

=SUM(COUNTIF(A27,search_items1))

Now, you can use SUMPRODUCT to avoid having to use CSE to use this formula:

=SUMPRODUCT(COUNTIF(A27,search_items1))

If however you have a cell containing LBNL LBNL and want the result to be 2, that's yet another matter, because COUNTIF's job is completed once it found what it was looking for in a cell and will return 1.

In that case, I would suggest counting each cell to be checked (because the above function can be used to look into whole columns):

=SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,search_items1,"")))/LEN(search_items1))

enter image description here

[Notice now that you need to drop the asterisks because of how SUBSTITUTE works.]

Related Question