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

Question

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?

Answer 1

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

Answer 2

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!

Answer 3

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.

Answer 4

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