|
| 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
March 23rd, 2010, 02:48 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
March 23rd, 2010, 03:20 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
March 23rd, 2010, 03:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
March 23rd, 2010, 03:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
March 23rd, 2010, 04:52 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 | |
|
|
Linear Mode
