Averageifs (the average for the past 3 months)

Occasional Contributor

Averageifs (the average for the past 3 months)

=averageifs(Inf_Sales_Data!P:P,Inf_Sales_Data!B:B,{\$G\$216,\$H\$216,\$I\$216},Inf_Sales_Data!T:T,{"Feedpost","Reel","Short","Short 2"})

My question is whether the syntax of this formula is correct. I am saying:

The average of cost (Inf_Sales_Data!P:P) if the month (Inf_Sales_Data!B:B) matches one of these values \$G\$216, \$H\$216, or \$I\$216 (which are the previous 3 months) and the content type (Inf_Sales_Data!T:T) is either a "Feedpost","Reel","Short","Short 2".

Corrections are welcome

5 Replies

Re: Averageifs (the average for the past 3 months)

No, that won't work. For one thing, it is not possible to specify cell references in an array {...., ...}. Moreover, AVERAGEIFS doesn't understand that you want to use the arrays as OR conditions.

Try this - adjust the ranges, but preferably don't use entire columns.

=AVERAGE(IF(((Inf_Sales_Data!\$B\$2:\$B\$250=\$G\$216)+(Inf_Sales_Data!\$B\$2:\$B\$250=\$H\$216)+(Inf_Sales_Data!\$B\$2:\$B\$250=\$I\$216))*((Inf_Sales_Data!\$T\$2:\$T\$250="Feedpost")+(Inf_Sales_Data!\$T\$2:\$T\$250="Reel")+(Inf_Sales_Data!\$T\$2:\$T\$250="Short")+(Inf_Sales_Data!\$T\$2:\$T\$250="Short 2")),Inf_Sales_Data!\$P\$2:\$P\$250))

If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

Re: Averageifs (the average for the past 3 months)

That should work:

``````=LET(
crit,--(ISNUMBER(XMATCH(Inf_Sales_Data!B:B,{\$G\$216,\$H\$216,\$I\$216}))+ISNUMBER(XMATCH(Inf_Sales_Data!T:T,{"Feedpost","Reel","Short","Short 2"}))>=1),
IFNA(SUM(crit*Inf_Sales_Data!P:P)/SUM(crit),0)
)``````

Re: Averageifs (the average for the past 3 months)

@brunomerola, doesnt work unfortunately

Re: Averageifs (the average for the past 3 months)

@Hans Vogelaar thanks, once again!
I understand the logic, thanks for the description.
Unfortunately your formula returns a divided by 0 error.
I cannot understand why in this case.

Re: Averageifs (the average for the past 3 months)

Did you confirm the formula with Ctrl+Shift+Enter?

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.