Thread: SUMIFS Closed Workbook Workaround with Multiple Criteria Array Thanks: 0 Likes: 0

1. SUMIFS Closed Workbook Workaround with Multiple Criteria Array

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/art...9-160ed5f13b5a

I found a previous forum post addressing a similar circumstance here: https://www.mrexcel.com/forum/excel-...-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?

2. Re: SUMIFS Closed Workbook Workaround with Multiple Criteria Array

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

Code:
`=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!