Linking data from another workbook using SUMPRODUCT

[mannybear]

I read a solution from a 2013 thread where FDibbins suggested using SUMPRODUCT instead of AVERAGEIF to pull data from another workbook.

"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"

"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"))"

Question 1: Will a version of this formula work if two conditions must be satisfied? i.e. Column A must = "Qualifying" and Column C must = "Red" ?
Question 2: Instead of calculating average, how would I calculate MEDIAN AND/OR MODE ?

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Using SUMPRODUCT to calculate averages from a closed workbook
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[mannybear]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Using SUMPRODUCT to calculate averages from a closed workbook
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I was not aware of that rule. I immediately closed the post on the other board.

 

[Fluff]

You do not need to close any posts, just let us know in future.