# Average numbers if another column contains a certain substring text in Excel

I'm looking for a way to average the numerical numbers in one column, say B, only if the string in column A contains a certain text.

For example:

``````(In column A) (In column B)
Hello table . . . 1
Hello desk  . . . 2
Bye table   . . . 3
Bye desk  . . . . 4
``````

So if the criteria to average the numbers is "desk" the answer would be (2+4)/2=3.

What I came up with so far is:

``````=AVERAGEIF(A1:A5, SEARCH("desk",B1:B5),B1:B5)
``````

but it returns an error because the SEARCH only takes one input.

Any suggestions?

Add the asterisk `*` wildcard character before and/or after the word “desk”, as:

``````=AVERAGEIF(A1:A5,"*desk",B1:B5)
``````

or

``````=AVERAGEIF(A1:A5,"*desk*",B1:B5)
``````

This tells Excel to ignore any characters before/after the word “desk”.

Well as a somewhat generic answer that will work for you, another way to do a condition within a function (in your case an IF inside an AVERAGE) you can always break it apart. The IF statement can also return an array and does not need to be a single value.

If you type

``````=AVERAGE(IF(ISERROR(SEARCH("desk",A1:A5))=FALSE,A1:A5))
``````

and press Ctrl + Shift + Enter after you type it, then it will treat the input as an array rather than a value, giving your results.

Hope this helps!

• This doesn't appear to do what was asked at all. This will average A1:A5 (the range with text in it) if any of the items in A1:A5 contain the word 'desk'. Even if you correct the range returned by the IF portion to B1:B5 it's still not work the way it needs to because it'll return all items and then only when no rows contains a match. – krowe2 Jul 27 '15 at 13:53

AVERAGEIF is the best answer for Excel 2013+. For older versions, you'll need to use a third column, C.

All cells in column C (with a value to the left) have this formula: `=IF(ISNUMBER(SEARCH(C\$7,A1)), B1, "")`

Cell, C7 just has the word `desk` typed out so that those formula don't need to be fixed every time you change the search.

After this, you simply do an average of that in C5, `=AVERAGE(C1:C4)`. That's all you need. Not a lot of wasted space really and you can get rid of the search box too if you'd rather just adjust the functions when needed.

I'm not sure if using column C is a possibility for you, but here is one option that I have tested.

In Cell C1, input the following formula: =IF(ISNUMBER(SEARCH("desk",A1)),B1,"")

Copy/paste this formula down for each row in column C

Average the amounts that appear in column C.

If column C is already used, just insert another column in its place