AverageIfS Function

[CleverUserName]

I am having trouble with this AverageIfS function. It seems to only calculate the average of the "A" criteria and is not including "UC" & "US" criteria. The result in this case I receive is 76 and it should be 62.6. Thank you for your help.

FORMULA:
{=AVERAGEIFS('GSMLS Data'!B:B,'GSMLS Data'!G:G,{"A","UC","US"},'GSMLS Data'!D:D,">="&N15,'GSMLS Data'!D:D,"<="&O15)}

In the formula N15 represents a value of: 500000 and O15 represents a value of: 600000

Spreadsheet: GSMLS Data
Column B Column C Column D Column E Column F Column G

DAYSONMARKET	EXPIREDATE	LISTPRICE	ORIGLISTPRICE	SPRICE	STATUS
132	00:00.0	529000	549000		A
76	00:00.0	559900	595000		A
20	00:00.0	579900	579900		A
7	00:00.0	519000	519000		UC
78	00:00.0	599000	657000		UC

<tbody>
</tbody>

 

[steve the fish]

This should work entered CTRL-SHIFT-ENTER:

=AVERAGE(IF('GSMLS Data'!D2:D100>=N15,IF('GSMLS Data'!D2:D100<=O15,IF((('GSMLS Data'!G2:G100="A")+('GSMLS Data'!G2:G100="UC")+('GSMLS Data'!G2:G100="US"))>0,'GSMLS Data'!B2:B100))))

 

[steve the fish]

Or possibly:

=SUM(SUMIFS('GSMLS Data'!B:B,'GSMLS Data'!D:D,">="&N15,'GSMLS Data'!D:D,"<="&O15,'GSMLS Data'!G:G,{"A","UC","US"}))/SUM(COUNTIFS('GSMLS Data'!D:D,">="&N15,'GSMLS Data'!D:D,"<="&O15,'GSMLS Data'!G:G,{"A","UC","US"}))

 

[DanteAmor]

Try:

=SUMPRODUCT(('GSMLS Data'!D2:D6000>=N15)*('GSMLS Data'!D2:D6000<=O15)*(('GSMLS Data'!G2:G6000="A")+('GSMLS Data'!G2:G6000="UC")+('GSMLS Data'!G2:G6000="US"))*('GSMLS Data'!B2:B6000))/COUNT('GSMLS Data'!B2:B6000)

 

[AhoyNC]

You might be interested in the following link that explains some different options.
https://stackoverflow.com/questions/50496724/excel-using-array-in-averageifs-formula