Average function and two criteria

[Paula M]

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

 

[Domenic]

Try...

=AVERAGE(IF((ARGENTINA!$A$1:$A$995="S")*(ARGENTINA!$B$1:$B$995="H"),ARGENTINA!F1:F995))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust th
ranges accordingly.

Hope this helps!

 

[Aladin Akyurek]

=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]

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

 

[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!

 

[Domenic]

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!

 

[Paula M]

Thank you again, Domenic!