Sumproduct formula linking to external workbook works in one file, but not another

Hi all,

I am having a problem with a SUMPRODUCT formula that I am using to reference an external workbook and am hoping to get a little help.

The formula is a replacement for a SUMIFS formula (which took long enough to figure out, but we got there), so the user will not have to have the external file open in order for the links to update properly. The problem I am having is that the formula works in one workbook, but not in others. Here is the formula: (SUMPRODUCT(--([Database.xlsx]Converted!$A:$A=C$1),--([Database.xlsx]Converted!$G:$G="d"),--([Database.xlsx]Converted!$E:$E))/1000)

So this formula works great in certain files, but in others it returns #VALUE and I can't figure out why. The excel versions are all the same so I don't think that would be it. Does anyone have any idea why a sumproduct formula would work in one workbook but not another? It's strange because I am certain the formula is correct. If I am in a file that works and change the value of C$1 to the actual text value from a file that doesn't work, it will work fine! So from what I can tell it's seems to be workbook based and not due to formula or data errors.

My forehead is getting sore from hitting the desk.

Help is greatly appreciated!