links using sumifs function

OK here is the situation. I have first Excel 2007 workbook with some data. I have a second Excel 2007 workbook which is using the SUMIFS function on some of the cells in the first workbook. The function works as I intend and has the correct values. When I open just the second workbook all the cells with the SUMIFS functions referencing the first workbook all come up with #VALUE. If I then open the first workbook with the second already open I get all the correct values in the second workbook. I have verified (in the second workbook) that under Data/Edit Links/Update is set to "Automatic", Startup Prompt is set to "Dont Display the Alert and update links", under Excel Options/Advanced/When calculating this workbook/the update links to other documents is checked. What am I missing to get the correct values to display when opening just the second workbook? I have heard that some functions are not supported when linking to another workbook. Is that true and if so what functions are not supported?

I tried the SUMPRODUCT function. I acts just like the SUMIFS except is displays #REF instead of #VALUE. Just like before when I open the first workbook the SUMPRODUCT function works as I intend and has the correct value.

Here is my original SUMIFS

=SUMIFS('[A.xlsx]Date'!$T:$T,'[A.xlsx]Date'!$B:$B,">="&$A3+1,'[A.xlsx]Date'!$B:$B,"<="&$A4)

$T contains integers
$B and $A contain dates

My SUMPRODUCT formula now works when I use specific ranges instead of the whole column. Thanks for the help