SUMIFS Closed Workbook Workaround with Multiple Criteria Array

[doesnt excel]

Hello,

I am having trouble figuring out a way to make this formula work when the source workbook is closed:

=SUM(SUMIFS('Workbook A.xlsx]Sheet1'!$E$2:$E$2000,'[Workbook A.xlsx]Sheet1'!$B$2:$B$2000,$A2,'[Workbook A.xlsx]Sheet1'!$D$2:$D$2000,{"Apples","Oranges"}))

This formula is working fine when Workbook A is open, and other fields in the form using a VLOOKUP referencing workbook A work when it's closed, so I believe this is an issue with the SUMIFS function.

Microsoft says this is a known issue here: https://support.office.com/en-us/ar...function-e65ce689-978e-44b1-a649-160ed5f13b5a

I found a previous forum post addressing a similar circumstance here: https://www.mrexcel.com/forum/excel-questions/990042-value-sumifs-referencing-closed-workbook.html

I couldn't figure out how to use the workarounds suggested there because I have a nested SUM and SUMIFS formula and I have an array in the criteria at the end.

Any suggestions?

Thank you in advance!

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF('[Workbook A.xlsx]Sheet1'!$B$2:$B$2000=$A2,IF(ISNUMBER(MATCH('[Workbook A.xlsx]Sheet1'!$D$2:$D$2000,{"Apples","Oranges"},0)),'[Workbook A.xlsx]Sheet1'!$E$2:$E$2000)))

Hope this helps!