SUMIF With "Indirect" link to closed workbook

[rehanahmad]

Hello all
I want to use a combination of Sumif and Indirect function to link to data in closed workbook in a folder location. I have wrote =SUMIF(INDIRECT("'["&Sheet2!C9&".xlsx"&"]"&Sheet2!D9&"'!"&"T:T"),BB14,INDIRECT("'["&Sheet2!C9&".xlsx"&"]"&Sheet2!D9&"'!"&"H:H")), where Sheet2!C9 and Sheet2!D9 are cells reference to file locations and sheet name respectively. The formula is giving "#REF!", can someone see what is wrong with the formula?

 

[RoryA]

Neither SUMIF nor INDIRECT will work with a closed workbook.

 

[rehanahmad]

Neither SUMIF nor INDIRECT will work with a closed workbook.

Dear RoryA
Thanks for your messages. SUMIF is already working with closed workbook, its just when I factor in the INDIRECT, it falls over.

 

[RoryA]

No, it doesn't. If the SUMIF recalculates while the source workbook is closed, it won't work. None of the xxxIF functions work with closed workbooks.

 

[rehanahmad]

No, it doesn't. If the SUMIF recalculates while the source workbook is closed, it won't work. None of the xxxIF functions work with closed workbooks.

Let me explain a bit more may be, my SUMIF is currently pulling data from another workbook via formula is below:
SUM(IF('E:\Bridge\ABC\Final Submitted\Q423\[ifrs_XEL_paa_ins 17.01.2024 _Batch 68.xlsx]T_INS_CO_IF'!$T:$T=D$10,'E:\Bridge\ABC\Final Submitted\Q423\[ifrs17_XEL_paa_ins 17.01.2024 v2_Batch.xlsx]T_INS_CO_IF'!$H:$H)) which is working fine whether the other workbook is open or not. Its only when I try to factor in the INDRECT function it doesnt like.

 

[RoryA]

Again, SUMIF will not calculate against a closed workbook; it never has. It will retain values between calculations, but if anything causes that formula to recalculate while the source workbook is closed, it will return an error. INDIRECT also will not work with a closed workbook and because it is volatile (i.e it will recalculate any time Excel calculates), it will inevitably refresh while the source workbook is closed, and cause errors. Neither function should be used with closed workbooks.

 

[jkpieterse]

@rehanahmad Your last post shows SUM(IF(, which is not the same as SUMIF. SUM(IF works on a closed workbook, but SUMIF and INDIRECT do not.

 

[rehanahmad]

Yes I know this is typo, its all part of SUMIF. Thanks for the reply.

 

[jkpieterse]

To prove it isn't working, open your workbook with the SUMIF formula (the one that you say works) and press F9 to force a calculation.

 

[RoryA]

I know this is typo

Impressive typo given that you have correct closing parentheses at the end.