AverageIFS

Hi there. try this:

=AVERAGEIFS(H:H,A:A,M36,G:G,{"Pending","Approved"})

You need SUMIFS/COUNTIFS like this

=SUM(SUMIFS(H:H,A:A,M36,G:G,{"Pending","Approved"}))/SUM(COUNTIFS(A:A,M36,G:G,{"Pending","Approved"}))

...or you can use an array formula like this.

=AVERAGE(IF(A:A=M36,IF(G:G={"Pending","Approved"},H:H)))

Confirmed with CTRL+SHIFT+ENTER

....bUt for the latter formula you should probably restrict the ranges, whole columns will be slow

Check for extra spaces before/after "approved" in G..

Originally Posted by rbirch

{=AVERAGEIFS($H$487:$H$489,$C$487:$C$489,"Gaming",$A$487:$A$489,{"Open-Pending Approval","Design Win-Pending Approval"},G487:G489,{"Pending","Approved"})}

This syntax won't work - you need to use the method I suggested - if you have an additional multiple criteria you need to use comma separators for one and semi-colons for the other - like this:

=SUM(SUMIFS($H$487:$H$489,$C$487:$C$489,"Gaming",$A$487:$A$489,{"Open-Pending Approval","Design Win-Pending Approval"},G487:G489,{"Pending";"Approved"}))/SUM(COUNTIFS($C$487:$C$489,"Gaming",$A$487:$A$489,{"Open-Pending Approval","Design Win-Pending Approval"},G487:G489,{"Pending";"Approved"}))

If you want to have more than two "multi-criteria" then you need to use the array formula approach I suggested

When you use an "array constant" as a criterion in AVERAGEIFS then the result is also an array - but there's no way to get a single average from that array (you can't average it because that wouldn't take account of the number of instances for each), hence the need for SUMIFS/COUNTIFS approach