SUMIF(By Date) - SUMIF(By Date)= #Value Error From Linked Data

Hey there:

I'm having an issue with using the following code in Excel 2010:

=SUMIF('I:\PMC\1 Current\05 Performance Reports\2012\2012-03\[Brazil 2012-03.xlsx]Project'!$Y$8:$Y$507,">="&DATE(2012,1,1),'I:\PMC\1 Current\05 Performance Reports\2012\2012-03\[Brazil 2012-03.xlsx]Project'!$AA$8:$AA$507) - SUMIF('I:\PMC\1 Current\05 Performance Reports\2012\2012-03\[Brazil 2012-03.xlsx]Project'!$Y$8:$Y$507,">"&DATE(2012,03,31),'I:\PMC\1 Current\05 Performance Reports\2012\2012-03\[Brazil 2012-03.xlsx]Project'!$AA$8:$AA$507)

The formula finds all values in range Y8:Y507 that are >= January 1, 2012 and sums up the corresponding values found in cell range AA8:AA507 that meet that criteria. It then repeats the finding of all values in range Y8:Y507 that are < March 31, 2012 and sums up the corresponding values in AA8:AA507 to subtract them from the first SUMIF formula to find the year-to-date total for Brazil.

The formula works beautifully when the linked worksheets are open. When they are closed, it returns a #Value error. After some research I have found that =SUMIF formula's by design have this problem and that the solution is either using a SUMPRODUCT formula or a CSE formula. Unfortunately, I have experimented with both and cannot seem to get the syntax right. Perhaps there is another way to tackle this issue that I'm not thinking of.

Could anybody help me with this? Thanks in advance!