Averageifs with OR criteria

**Subject_Name_Here** · 05-28-2012, 12:43 PM

I have a spreadsheet attached. Basically, I have 4 products - A, B, C, and D. Their production rates are shown in column B. I need to take the average of both A and B's rates together in a single cell.

I've tried
=AVERAGEIFS(rates column, product names, "A", product names, "B")
or...
=AVERAGEIFS(rates column, product names, OR("A","B"))

Unsuccessful.

The method using sumif/countif works, but it's too bulky. There must be a way to do this with averageifs.

Any ideas would be appreciated. Thanks.

**veejar** · 05-28-2012, 12:51 PM

Just use Averageif for A and B just sum them that should work

**veejar** · 05-28-2012, 12:53 PM

Code for Cell I3 in your Sheet

Code: =(AVERAGEIF($A$2:$A$12,E2,$B$2:$B$12)+AVERAGEIF($A$2:$A$12,E3,$B$2:$B$12))/2

**Subject_Name_Here** · 05-28-2012, 12:59 PM

Summation and division of the averages will only work if there are an equal number of terms per product.

**veejar** · 05-28-2012, 01:06 PM

Didn't get what you are trying to say. Do you mean that if number of A's are 3 and number of B's are 2 then the code mentioned in Post 3 won't work

**daddylonglegs** · 05-28-2012, 01:57 PM

Originally Posted by Subject_Name_Here

Summation and division of the averages will only work if there are an equal number of terms per product.

That's right....but the same principle also makes it difficult to use AVERAGEIFS. You can either use SUMIF/COUNTIF as you suggest, e.g.

=SUMPRODUCT(SUMIF(A$2:A$12,E$2:E$3,B$2:B$12))/SUMPRODUCT(COUNTIF(A$2:A$12,E$2:E$3))

...or use this "array formula"

=AVERAGE(IF(COUNTIF(E$2:E$3,A$2:A$12),B$2:B$12))

confirmed with CTRL+SHIFT+ENTER