Average function and two criteria

Hi!
I'd appreciate your help with a formula. I'm trying to get the avarage ages of all the people on a list that meet two different criteria. The Ages are in column F and the criteria are in in columns A and B.
Could you please tell me what's wrong with my formula? I'm entering it as an Array formula.


{=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}

Many thanks

Paula

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

THANK YOU BOTH!!
The two options worked just fine!

Hi,
I need your help again. Now, I need to know the avarage age of the people belonging to the Sales deparment that are either category "C" or "H".
Column A has the different departments (S, F, D, E, etc), column B, the different categories (C, H, B, etc), and column F has the employee ages.
I thought of this:

=((AVERAGE(IF((ARGENTINA!$A$1:$A$999="S")*(ARGENTINA!$B$1:$B$999="C"),ARGENTINA!$F$1:$F$999)))+(AVERAGE(IF((ARGENTINA!$A$1:$A$999="S")*(ARGENTINA!$B$1:$B$999="H"),ARGENTINA!$F$1:$F$999))))/2
Entering it with Ctrl+shift+enter as an array formula.

It works fine when both averages are greater than 0, but it is no good when one of them is 0. Any ideas to solve my problem? All suggestions are welcomed!

Thanks for your help!

Try...

=AVERAGE(IF((ARGENTINA!$A$1:$A$999="S")*(ISNUMBER(MATCH(ARGENTINA!$B$1:$B$999,{"C","H"},0))),ARGENTINA!$F$1:$F$999))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Originally Posted by Paula M

Hi,
I need your help again. Now, I need to know the avarage age of the people belonging to the Sales deparment that are either category "C" or "H".
Column A has the different departments (S, F, D, E, etc), column B, the different categories (C, H, B, etc), and column F has the employee ages.
I thought of this:

=((AVERAGE(IF((ARGENTINA!$A$1:$A$999="S")*(ARGENTINA!$B$1:$B$999="C"),ARGENTINA!$F$1:$F$999)))+(AVERAGE(IF((ARGENTINA!$A$1:$A$999="S")*(ARGENTINA!$B$1:$B$999="H"),ARGENTINA!$F$1:$F$999))))/2
Entering it with Ctrl+shift+enter as an array formula.

It works fine when both averages are greater than 0, but it is no good when one of them is 0. Any ideas to solve my problem? All suggestions are welcomed!

Thanks for your help!

Thank you again, Domenic!

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>

=AVERAGE(IF(ARGENTINA!$A$1:$A$995="S",IF(ARGENTINA!$B$1:$B$995="H",ARGENTINA!F5:F999)))

which must be confirmed with control+shift+enter, not just with enter.

Paula M wrote:
> Hi!
> I'd appreciate your help with a formula. I'm trying to get the avarage
> ages of all the people on a list that meet two different criteria. The
> Ages are in column F and the criteria are in in columns A and B.
> Could you please tell me what's wrong with my formula? I'm
> entering it as an Array formula.
>
>
> {=IF(((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H")),AVERAGE(ARGENTINA!F5:F999),0)}
>
> Many thanks
>
> Paula
>
>