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!

Hi, welcome to the forum

1st, I would suggest that you avoid full-column ranges in SP, they can make your file begin to slow down
2nd are you sure that the file names are correct? (no extra spaces anywhere?)

Hi FDibbins,

I adjusted the ranges so the are not full columns and now it works! I had known that you shouldn't use full columns with SP, but it worked initially with my first try so I didn't think about it. No idea why it would work in some files and not others, but who cares I can finally move on.

Thanks for your help!

Glad it worked out for you