Column Average Given 2 Criteria

[AAA1986]

I want to get the average for a certain column, given 2 different criteria.
Right now I'm using this formula for one criteria:

=SUMIF($Q$3:$Q$114,"=10",$E$3:$E$114)/COUNTIF($Q$3:$Q$114,"=10")

I want to also include that R3:R114 equals 1.

Thanks

 

[Luke M]

You could use this array* function:

=AVERAGE(IF(($Q$3:$Q$114=10)*($R$3:$R$114=1),$E$3:$E$114))

*Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter

 

[Fred Smith]

For more than one criteria, use Sumproduct, as in:
=SUMPRODUCT(($Q$3:$Q$114=10)*($R$3:$R$114=1)*$E$3:$E$114)/SUMPRODUCT(($Q$3:$Q$114=10)*($R$3:$R$114))

Regards,
Fred

 

[Elkar]

Try this:

=SUMPRODUCT(--($Q$3:$Q$114=10), --($R$3:$R$114=1), $E$3:$E$114) /
SUMPRODUCT(--($Q$3:$Q$114=10), --($R$3:$R$114=1))

HTH
Elkar

 

[T. Valko]

If you're using Excel 2007...

=AVERAGEIFS(E3:E114,Q3:Q114,10,R3:R114,1)