Help with the "AVERAGEIF" function

[andrewpzim]

Not sure how to word this but here it goes..

I have a column(H) with how many miles out of route a driver went. The next column(I) is if the driver was due home or not.

on I45 I have a cell labeled "company OOR Average for drivers due home". I want this cell to have a formula that will average H25:H38, only if I25:I38 has "YES" in the cell, and plugs the information in I45.

I have attached a picture with the information above to give a better understanding.

[PaulM100]

=averageif($i$25:$i$38,"yes",$h$25:$h$38)

[andrewpzim]

That is what I thought originally but without the "$". I have tried with and without the "$" now, and both are coming back "#DIV/0".

[PaulM100]

Try this:

=iferror(averageifs($h$25:$h$38,$i$25:$i$38,"yes",$h$25:$h$38,"<>"&0),"")

[andrewpzim]

no-go on that one as well. Accepted the formula without any error codes but now the cell is completely empty.

[xlnitwit]

Hi,

It sounds like nothing is meeting your criteria. What about this
=averageif($i$25:$i$38,"*yes*",$h$25:$h$38)

[andrewpzim]

That did it! Thank you all for your help!

[xlnitwit]

You're welcome. That suggests that your cells have leading or trailing spaces and not simply "yes".