Column Average Given 2 Criteria

  • Thread starter Thread starter AAA1986
  • Start date Start date
A

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
 
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
 
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
 
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
 
Back
Top