AVERAGEIFS - trouble with argument for criteria_2

[mpresto75]

I am struggling with my formula - specifically, the argument for criteria_2. I would like to find the average of "complete", "1st dn", and "td". If any of the three are true, I would like to average the gain. I have attached my data set. Here is my formula:

=AVERAGEIFS(H2:H96,M2:M96,"Pass",N2:N96,"complete","1st dn","td"

[Tony Valko]

Try this...

Use cells to hold the criteria:

AB2 = Pass

AC2 = Complete
AC3 = 1st dn
AC4 = td

This array formula**:

=AVERAGE(IF(M2:M96=AB2,IF(ISNUMBER(MATCH(N2:N96,AC2:AC4,0)),H2:H96)))

** 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.

[daddylonglegs]

You won't be able to do that with AVERAGEIFS, you can use this version

=SUM(SUMIFS(H2:H96,M2:M96,"Pass",N2:N96,{"complete","1st dn","td"}))/SUM(COUNTIFS(M2:M96,"Pass",N2:N96,{"complete","1st dn","td"}))

or with an "array formula"

=AVERAGE(IF(M2:M96="Pass",IF(ISNUMBER(MATCH(N2:N96,{"complete","1st dn","td"},0)),H2:H96)))

confirmed with CTRL+SHIFT+ENTER

[mpresto75]

That worked brilliantly! Thank you very much for the help. I cannot tell you how grateful I am.

Mike

[Tony Valko]

You're welcome. Thanks for the feedback!