Excel sum if and closed workbooks

[Matt1234]

Hi,

Had a look through and found that maybe a sumproduct will allow this formula to update with a closed workbook but i keep getting a ref or value error when the sheet is closed, it works fine when the other workbook is open, any help would be much appreicated

=SUMIFS(INDIRECT("'[EURO.XLS]"&L1&" 2018'!$H$13:$H$75"),INDIRECT("'[EURO.XLS]"&L1&" 2018'!$A$13:$A$75"),">="&$B$15,INDIRECT("'[EURO.XLS]"&L1&" 2018'!$A$13:$A$75"),"<="&$B$90,INDIRECT("'[EURO.XLS]"&L1&" 2018'!$B$13:$B$75"),"*INT*")

Thanks Matt

 

[Becky]

This is a known problem with Excel unfortunately, but there is a workaround:

https://support.microsoft.com/en-gb...f-and-countblank-functions-return-value-error

 

[AmjiBhai]

I wish I had your file(s) with me I would experiment as follows:

1.Open All files and ascertain that your formula is working.
2. Keeping your target formula file open, I would close your EURO.XLS file(s) and see the file path is automatically get embedded into your formula. This is a normal behaviour of MSExcel.
3. Once All files are closed except the file which has your SUMIF(INDIRECT formula, carefully watch what happens when you close this file, it should display an alert "Recalc before close".
4. Now re-open your target file which contains your SUMIF(INDIRECT formula,carefully watch what happens when you open this file,
5. Findings of 1-4 will help me decide what to do.

Nevertheless, you can do it in a down-right way...the action that doesn't care whether your source files (EURO.XLS etc) are available(open) or not. It would read from the specified ranges of the closed workbook and here you go.. but that requires VBA-and-ACCDB, unfortunately I found most users specially accountants - reluctant to utilize VBA. Its my personal opinion.