Value error sumif external link

[Starbucks_33]

Hi,

I am using excel 2007. I have a Sumif formula linked to another workbook. Some times the "Value" error pops up seemingly at random. This goes away if I open up the linked workbook and then close it.

Is there something causing this error + how to fix.

Thanks!

 

[Richard Schollar]

Yes - if the target workbook is going to be closed, you need to use SUMPRODUCTs, not SUMIFs as SUMIFs fail when the workbook they reference isn't open.

 

[RoryA]

SUMIF does not work with closed workbooks. (nor does COUNTIF). You should use SUMPRODUCT instead if you need the other workbook closed.