Can't get External References to Work in Formula

[3link]

I have about 20 different spreadsheets that will be used for data entry. Unfortunately it isn't feasible to put all the data entry into a single spreadsheet. These data entry sheets are my source workbooks. For simplicity sake, the source workbooks will have three columns: A B and C.

I have created a summary workbook that summarizes the data in Column C of the 20 source workbooks. I used a sumifs formula to take the sum of C every time A and B equal a certain value.

The summary workbook works fine when and only when I have the source workbooks open. But if the source workbooks are not open, then the formula in the summary workbook returns a "#Value!" error.

Is there any way to make formulas in the summary to work without having the source workbooks open? Or is it the case that formulas with external references will not work unless the source workbooks are open? Maybe it depends on the function I'm using?

Update: It seems that formulas with index and match will work even if the source workbook is closed. The problem is that I can't think of a way to get an index match function to do what I need here. I only want C cases where A and B equal a certain value. (There will only be one case where A and B equal a certain value. I figured Sumifs is the best function here but maybe there is an alternative?)

 

[3link]

I figured out a potential workaround, though it isn't perfect.

It appears that sumifs doesn't work when used with references to external workbooks unless those workbooks are open. However, index/match functions work perfectly fine.

The problem with index match is you can typically only use it to look up a value based on a single criterion. But I discovered you can make an array variant of the index match function that will look up multiple criteria. See here. And I confirmed that the array variant of the index/match function works even if the source workbook is closed.

The problem is that array formulas are inherently resource hogs. And this summary sheet of mine will have over 1,200 lines. That's 1,200 instances of an array formula. I foresee this spreadsheet having latency issues. So I would appreciate it if any has a better solution they can share.