If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|