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
[TABLE="width: 590"]
<tbody>[TR]
[TD]DAYSONMARKET[/TD]
[TD]EXPIREDATE[/TD]
[TD]LISTPRICE[/TD]
[TD]ORIGLISTPRICE[/TD]
[TD]SPRICE[/TD]
[TD]STATUS[/TD]
[/TR]
[TR]
[TD="align: right"]132[/TD]
[TD="align: right"]00:00.0[/TD]
[TD="align: right"]529000[/TD]
[TD="align: right"]549000[/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]76[/TD]
[TD="align: right"]00:00.0[/TD]
[TD="align: right"]559900[/TD]
[TD="align: right"]595000[/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]00:00.0[/TD]
[TD="align: right"]579900[/TD]
[TD="align: right"]579900[/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]00:00.0[/TD]
[TD="align: right"]519000[/TD]
[TD="align: right"]519000[/TD]
[TD][/TD]
[TD]UC[/TD]
[/TR]
[TR]
[TD="align: right"]78[/TD]
[TD="align: right"]00:00.0[/TD]
[TD="align: right"]599000[/TD]
[TD="align: right"]657000[/TD]
[TD][/TD]
[TD]UC

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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