Average if two criteria matched

**Actual** · 04-07-2009, 07:04 AM

Hey!

For example say I have a table array called Cheese, column A is numbers, B is country of origin, and C is Smelly/Not smelly.

I want to get the average from column A, but only for cheeses that are, say, from England and smelly.

I've played with sumif/countif but couldn't make it work for two criteria, can anyone help?

**DonkeyOte** · 04-07-2009, 07:29 AM

Given you're using XL2007 you can make use of the new AVERAGEIFS function

=AVERAGEIFS(A1:A100,B1:B100,"England",C1:C100,"Smelly")

If you need a backwards compatible version you can use an Average array

=AVERAGE(IF((B1:B100="England")*(C1:C100="Smelly"),A1:A100))
confirmed with CTRL + SHIFT + ENTER

The former if viable is preferable.