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!