average ifs

[lejanco]

Hello,

I am trying to find the values in column J of the PO tracker. It should be the average of columns AF-AG on the PO tracker, on the conditions the value in column b on the po tracker matches column A on the us sales summary. I am using this formula, but it is not working. Help appreciated! thanks!
=AVERAGEIFS('US Sales Summary'!AF5:AH296,'US Sales Summary'!A5:A296,"="&'PO Tracker'!B2)

Ideally, it would be nice to include a date formula for the past three months (oct, nov, dec). That will automatically update.

Thanks!
Laura

[AmirSoft]

SOLVED
<<<<<<<<<<<< Add Reputation

[protonLeah]

J2:

Please Login or Register  to view this content.

I don't see any way to find where the last three months would be by a formula.

[FDibbins]

For the 3-month average, try this...
=AVERAGEIF('US Sales Summary'!A5:A296,"="&'PO Tracker'!B2,OFFSET('US Sales Summary'!$W$5,1,MATCH('PO Tracker'!L2,'US Sales Summary'!$W$5:$AH$5,0)-1,291,3))

[lejanco]

Thanks to everyone for your help!

[FDibbins]

happy to help and thanks for the feedback L(