Help with linked formulas not calculating

[Kemidan2014]

I made a workbook to reference another persons workbook to do some countifs to help with accountability.
When both books are open the formulas update just fine
When i close the source book i get #VALUE! error

1) i know the formulas themselves are calculating correctly when the source workbook is OPEN so its not a formula syntax error
2) the formulas when the SOURCE workbook is CLOSED, Display the correct file path for the source book where it is located
3) My workbook and source workbook are on the same Drive of the Network
4) Confirmed Calculation options are set to Automatic.

What could be other reasons why these externally linked referenced formulas are not updating when the i open my work book but leave the source book closed?

 

[Fluff]

You cannot use any of the xxxIF(S) functions on a closed workbook, you would need to use sumproduct instead.

 

[Kemidan2014]

Oooh see now that i did not know,

Here is the formula I am currently using, this basically repeated multiple times because for example "F3" is just the cell to the left of this formula, D1 amd H1 is a cell with the Year and a cell with the Month in number format.

=COUNTIFS('[CORE PIN TRACKING SHEET.xlsx]984 TCC2 SHEET'!$D:$D,F3,'[CORE PIN TRACKING SHEET.xlsx]984 TCC2 SHEET'!$A:$A,">="&DATE($D$1,$H$1,1),'[CORE PIN TRACKING SHEET.xlsx]984 TCC2 SHEET'!$A:$A,"<="&DATE($D$1,$H$1,31))

how do i achieve the same result but using SumProduct because i had difficulty using SUMPRODUCT before i ended up using countifs instead. i feel like i dont quite fully understand how Sum product works and cant get the Syntax right.

essentially i am asking "tell me how many times Core pin# identified by Column F(Row#) is replaced in Year D1 and month H1"

I am not asking for a perfect formula to copy paste just some guidance.

source workbook: Column D is where Core pin# is entered and Column A is the Date replaced.

 

[Fluff]

Roughly how many rows of data do you have on the "984 TCC2 SHEET" sheet?

 

[Kemidan2014]

right now, there are 5 lines,

but over time this will grow and more lines will be added

 

[Fluff]

Ok how about

=SUMPRODUCT(('[CORE PIN TRACKING SHEET.xlsx]984 TCC2 SHEET'!$D$2:$D$1000=F3)*('[CORE PIN TRACKING SHEET.xlsx]984 TCC2 SHEET'!$A$2:$A$1000>=DATE($D$1,$H$1,1))*('[CORE PIN TRACKING SHEET.xlsx]984 TCC2 SHEET'!$A$2:$A$1000<=DATE($D$1,$H$1,31)))

 

[Kemidan2014]

That works perfectly. i think i now have a better understanding of the Syntax for SUM PRODUCT

it actually seems to process and open must faster now. Thank you for your help

 

[Fluff]

You're welcome & thanks for the feedback.