Column Average Given 2 Criteria

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
 
L

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
 
F

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
 
E

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
SUMIF/COUNTIF with missing data 4
Average in a month/year 7
Averageifs - criteria is cell is not blank 1
Finding the weighted average of text that have been given values 1
Return a value from Chart 1 depending on the value typed into char 1
Conditional formatting Excel 2007 4
Countif and sumif combination problem 2

Top