Linking data from another workbook using AVERAGEIF

[Drew_]

Hi all.

I am using AVERAGEIF formulas to pull data from another workbook, and it works great as long as the source book is open. When it is closed, a #VALUE error returns. I had this same problem with my SUMIF formula but I broke it down and entered it as an array and that returns the correct value when the source book is closed. How do I get my AVERAGEIF formulas to update correctly when the source book is closed? Thank you

 

[FDibbins]

Short answer, you cant, it does not work on closed workbooks.

You could probably get around that using SUMPRODUCT though, what does your formula look like?

 

[Drew_]

SUMPRODUCT is what I was thinking, but I'm not very proficient with the formula. As of now, this is my formula:

=AVERAGEIF('[Ty stats.xlsx]Averages'!$A$2$:$A$61,"Qualifying",'[Ty stats.xlsx]Averages'!F2:F61)

Basically, if anything in the A column on the Averages sheet in Ty's book says "Qualifying", I want to take the Average of the F column. It's not too complicated, I just want it to work when Ty's workbook is closed

 

[FDibbins]

Maybe something like this?
=sumproduct(--('[Ty stats.xlsx]Averages'!$A$2$:$A$61="Qualifying"),'[Ty stats.xlsx]Averages'!F2:F61)/sumproduct(--('[Ty stats.xlsx]Averages'!$A$2$:$A$61="Qualifying"))

 

[Drew_]

You are a genius and a lifesaver my friend. Much appreciated!

 

[FDibbins]

Thanks for the kind words, happy to help

 

[Drew_]

One more quick question. The formula you kindly provided is averaging columns that have #DIV/0 in them because those cells have not been populated with data yet. Is there a way for my SUMPRODUCT formula to ignore these errors and calculate only the cells that have numbers in them?

I know for certain formulas you can use <>#DIV/0! or something like that, would that work here?

 

[Jonmo1]

I think you'll be best served solving the #Div/0! errors in the source book with an Iferror type of function.

If you can't do that, then use a plain old link formula to pull the data into this book, and go back to your standard averageif..

So do
In column A of the open book, do
=IFERROR('[Ty stats.xlsx]Averages'!A2,"")
And in column B
=IFERROR('[Ty stats.xlsx]Averages'!F2,"")

Fill both down

Then use your original averageif function using these columns.

 

[Drew_]

Fixing the source book with the IFERROR function solved my issue. Thank you Jonmo!

 

[Jonmo1]

You're welcome.