AVERAGEIFS using two different criteria for one range

[KomicJ]

I just can't figure it out...

=IFERROR(AVERAGEIFS(TEAM[AGE],TEAM[ACT],"X",TEAM[POS],"LW"),"-")

That works, but TEAM[POS] should be "LW" or "AG".

I know I could use a combination of SUMIFS divided by COUNTIFS, but do you see any other way to achieve what I'm looking for ?

[BadlySpelledBuoy]

Have a look at this thread:
http://www.excelforum.com/excel-form...statement.html

Post a sample workbook if you need more help than that.

BSB

[Jonmo1]

I see 3 possibilities

1. sumifs/countifs as you mentioned
2. A helper column with =OR(lw,ag)

3. This depends on what the possible values of POS are.
Are there only 3 possible values for POS, say LW AG and one other like SR or whatever.
Then use the criteria "<>SR"


Make that 4, an array formula as described in the thread posted before me.

[Tony Valko]

This array formula**:

=IFERROR(AVERAGE(IF(TEAM[ACT]="X",IF(ISNUMBER(MATCH(TEAM[POS],{"LW","AG"},0)),TEAM[AGE]))),"-")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[KomicJ]

Tony's solution worked like a charm, thank you everyone for your input.

[Tony Valko]

You're welcome. We appreciate the feedback!