SUMIF formulas return "#Value!" (linked files)

I have two computers: one XP, one Windows 2000. This problem only occurs on the XP.

The problem: File B is linked to File A using SUMIF formulas. If File A is not open, the File B cells with SUMIF formula linked to File A will return the "#VALUE!" error. This occurs whether I select Update or Don't Update to the "file is linked" question.

The same file works fine on the Windows 2000 computer.

Re: SUMIF formulas return "#Value!" (linked files)

Quote from Domenic

Try using SUMPRODUCT instead. As an example, the formula would be something like this...

=SUMPRODUCT(--('[Workbook.xls]Sheet1'!A2:A100=D2),'[Workbook.xls]Sheet1'!B2:B100)

Hope this helps!

Thanks, that has seemed to work.

I replaced one cell with the sumproduct formula and it stoped the other cells reverting to "#Value!".

Re: SUMIF formulas return "#Value!" (linked files)

Quote from Dave Hawley

Zymurgy1, are you sure this ONLY happens on XP?

Yes, same file opened on two differenent systems produces #Value! on the XP and the correct numbers on Windows 2000

Re: SUMIF formulas return "#Value!" (linked files)

After a bit of testing I found that if File B (as mentioned in the first post) is saved using Excel 2000, then opened using Excel 2003, the SUMIF links will return "#Value!". Other formulas such as the sumproduct suggested above work ok.

Thanks for the help.