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

Question

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.

Answer 1

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))

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

Answer 2

It does not always need to be a formula. It can be a range too. This allows you to keep a spreadsheet clean.

You can define a named array via the name manager. Call the name manager from the formula ribbon, create a New name. Give it a clear name (e.g. "BinWithMarks") and then paste this into the 'Refers to:' field:

={100,89,84,79,74,69,64,59,54,49,39,0}

Similarly, you can add the relevant grades as follows, using Grades as name:

={"A+","A","A-","B+","B","B-","C+","C","C-","D","E"}

After confirming your choices you can use this array to look up the relevant grade for a student using a the combination of index and match formulae.

=INDEX(Grades,MATCH(N11,BinWithMarks,-1))