SUMIFS to reference a closed workbook

[kong1802]

I've searched to find the answer and cannot get any solutions to work for me.

I'm running sumifs in a workbook to reference a closed workbook. It works fine with the referenced workbook is open, but as soon as it is closed, I get the #Value error.

Here is my formula:

=SUMIFS('[Location Timeline - 11.20.2017.xlsx]Timeline'!$MD:$MD,'[Location Timeline - 11.20.2017.xlsx]Timeline'!$A:$A,A12,'[Location Timeline - 11.20.2017.xlsx]Timeline'!$F:$F,"Total Rent")

Thanks for any help!

 

[Aladin Akyurek]

You need to switch to an array-processing formula... Something like one with SumProduct, for example:

=SUMPRDUCT('[Location Timeline - 11.20.2017.xlsx]Timeline'!$MD:$MD,('[Location Timeline - 11.20.2017.xlsx]Timeline'!$A:$A=A12)*('[Location Timeline - 11.20.2017.xlsx]Timeline'!$F:$F="Total Rent"))

Try to avoid the whole column references though for reasons of efficiency.