Help with SUMIFS with closed workbook

I have 3 workbooks that I pull data out of for metrics that is it's own workbook. I now know that SUMIFS will not work in this scenario unless the other workbooks are open. Below is an example formula that I am using to pull data from one of my sheets. Would someone be willing to assist in recreating this formula into something that will work with a closed workbook source?

Formula:

Please Login or Register  to view this content.

Any assistance would be appreciated.

Thanks

Mike

Here are scrubbed versions of the spreadsheets that I am using. There are more worksheets on these,and I think I can recreate the formulas for the other sheets and workbooks.

2014 PC Metrics.xlsx
2014 PC Orders.xlsx

Thanks

Pepe, I tried using SUMPRODUCT earlier with no luck. I am using a date range with the SUMIFS and was not able to recreate it with the SUMPRODUCT formula. I will continue to figure it out on my own as well, but if someone can supply to proper formula code, I would really appreciate it.

Thanks

I did not put full path, I will attempt that.

Thanks

Appreciate all the help provided. Putting the dates in a cell and referencing those cells was the key. I created hidden columns to hold that information. My new formula is:

Formula:

Please Login or Register  to view this content.

If it doesn't auto-populate the full path, I will manually do that.

Thanks