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

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.

-
btw I am trying to avoid VBA if at all possible – daniellopez46 Sep 9 '13 at 17:58
It seems to be working for me and I'm on Excel 2007. The only issue with the current formula is that you're counting in cell A27 only. Also, make sure to use Ctrl+Shift+Enter since the formula is an array function (or use `SUMPRODUCT` instead of `SUM`). The namedrange can contain the asterisk and will be considered as wildcard. – Jerry Sep 9 '13 at 18:04
will what I'm trying to do is for each cell (thus the single cell reference instead of a range) count number of instances for example cell A1 might have "LBNL" or "LBL" or both. In the case of the first two the formula should return 1 and in the case of the second 2. For me it only works for the first item in the name range. So it will count a cell with single text of "LBNL" but if it has "LBNL LBL" it will return 0 instead of 2. Are you sure its working for you? Does it return 2 for you in the latter example? – daniellopez46 Sep 9 '13 at 18:16
BTW I tried CSE and using sumproduct and using a range instead but still didn't work: {=SUMPRODUCT(COUNTIF(A27:A27,search_items1))} – daniellopez46 Sep 9 '13 at 18:18
Okay, what you are describing now is not what I had in mind after reading your question. I thought that the column had many comments, and you want to count the number of cells having `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 now must put 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

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.]

-
You know I was wondering about that last scenario but didn't even think to press my luck and ask. Thanks again. Great job! – daniellopez46 Sep 9 '13 at 20:44
@daniellopez46 I was looking for a way to get all the counts in the last part in a single cell, so so far I've had no luck ^^; I guess you'll have to bare with me for the time being. If I find one alternate before someone else does, I'll let you know! – Jerry Sep 9 '13 at 20:48

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