Column Average Given 2 Criteria
 

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

Column Average Given 2 Criteria
 

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

--
Best Regards,

Luke M
"AAA1986" wrote in message
...
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

Column Average Given 2 Criteria
 

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

"AAA1986" wrote in message
...
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

Column Average Given 2 Criteria
 

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


"AAA1986" wrote:

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

Column Average Given 2 Criteria
 

If you're using Excel 2007...

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

--
Biff
Microsoft Excel MVP


"AAA1986" wrote in message
...
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