AverageIFS formula on another workbook

[ClausSkeel]

Hi All.

I want to pull an average from the last 2 years in another workbook, but it only works when the other workbook is open.
The formula i have now is:

=AVERAGEIFS(Path..$BU:$BU;Path..$B:$B; B4; Path..$F:$F; ">"&TODAY()-(Q8*360))

BU:BU = Numbercolumn
B:B = Materialnumbers
B4 = Materialnumber
F:F = Dates

Q8 = a cell where you can input years

are there any work around to this problem?

 

[Alex Blakenburg]

I haven't been able to test it so not sure I have quite the right number of brackets but try something like this:

=Sumproduct((Path..$BU:$BU)*(Path..$B:$B= B4)*(Path..$F:$F>(TODAY()-(Q8*360))))
/ Sumproduct((Path..$B:$B= B4)*(Path..$F:$F>(TODAY()-(Q8*360))))

Since you have MS365 I believe this should work as well.

=Sum((Path..$BU:$BU)*(Path..$B:$B= B4)*(Path..$F:$F>(TODAY()-(Q8*360))))
/ Sum((Path..$B:$B= B4)*(Path..$F:$F>(TODAY()-(Q8*360))))