SUMIFS linked to another workbook brings back VALUE Error when data source not open

[ardanni90]

Hello all,

How can I have my SUMIFS formula return values when the data source is not open. I have tried adding an array but that does not work. I read about trying to convert the SUMIFS to AND or OR functions but I cant seem to figure out how to do that.

Here is the current formula:

=-SUMIFS('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$9:$9,'[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$2:$2,C$5)/1000

Any information would be very helpful!

Thanks,
Adam

 

[Special-K99]

Use SUMPRODUCT ?

 

[ardanni90]

I get a VALUE error when I do that. Even when the data source is still open.

Can you help convert that formula into one where SUMPRODUCT would work?

 

[Special-K99]

Untested

=SUMPRODUCT(('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$9:$9)*('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$2:$2=C$5))/1000

 

[ardanni90]

Untested

=SUMPRODUCT(('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$9:$9)*('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$2:$2=C$5))/1000

Thank you! That worked. Do you know if there different function that would work for AVERAGEIS?

=-AVERAGEIFS('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$9:$9,'[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$2:$2,">="&Dates!$E$7,'[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$2:$2,"<="&Dates!$E$8)/1000

 

[Special-K99]

Untested (check the results)

=-(SUMPRODUCT(('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$9:$9)*('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$2:$2>=Dates!$E$7)*('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$2:$2<=Dates!$E$8)))/(SUMPRODUCT(--(('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$9:$9)*('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$2:$2>=Dates!$E$7)*('[2018 Debt Dashboard Model.xlsx]Debt with BMG'!$2:$2<=Dates!$E$8))))/1000