Count with multiple criteria from a closed workbook

Hi all,

I am currently using the COUNTIFS formula to get the information I need when the workbook is open but I would like to be able to pull these counts without opening the workbook. Here is the formula that is working correctly while the workbook is open:

=COUNTIFS([TT_Output_All.xlsx]Sheet1!$C:$C,D$1,[TT_Output_All.xlsx]Sheet1!$F:$F, "2014",[TT_Output_All.xlsx]Sheet1!$I:$I,$A17,[TT_Output_All.xlsx]Sheet1!$L:$L, "production: am owner")


If someone could please help me convert this formula to an array that doesnt require me to open the workbook to get the counts it would be greatly appreciated.


Thanks in advance!

I tried that solution earlier today and it worked when the workbook was open but when I closed it the count changed to "0" (Correct count is 4). Any idea why that might have happened? I will try the SUMPRODUCT formula again and see if I entered it incorrectly earlier.

Oh and I tried it with and without CTRL+SHIFT+ENTER

=SUMPRODUCT(('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$C:$C=D$1)*('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$F:$F="2014")*('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$I:$I=$A17)*('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$L:$L="production: am owner"))

(using CTRL+SHIFT+ENTER also)

Here is the formula that is giving me "0" when the workbook is closed. Does it have something to do with the fact that some of the criteria is text and not numbers?

Updated the Formula but I am still getting 0.

=SUMPRODUCT(('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$C$1:$C$10000=D$1)*('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$F$1:$F$10000=2014)*('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$I$1:$I$10000=$A17)*('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$L$1:$L$10000="production: am owner"))

updated the formula again. Still getting "0"

=SUMPRODUCT(--('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$C:$C=D$1),--('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$F:$F=2014),--('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$I:$I=$A17),--('Z:\Operations\Reporting\DataWarehouse\Production Quality\[TT_Output_All.xlsx]Sheet1'!$L:$L="production: am owner"))