SUMIFS linked to other workbook, returns #VALUE!
 

Besides 2016 data in my 2016 workbook, I have there 2015 data for comparison purposes, with SUMIFS formulas linked to my 2015 workbook. The 2016 workbook presents the correct 2016 values but return an #VALUE! message for the 2015 values, when I open the 2016 workbook and the 2015 workbook is closed. (Clicking “ignore links” or “Update” doesn’t solve the issue). Only when I click “Edit Link” and “Open Source” – effectively opening the 2015 workbook - I get the correct (2015) data. )

I have 720 cells linked to the 2015 workbook in my 2016 workbook, in (the 2016) cell F3 I have the following formula:
=SUMIFS
('Macintosh HD:Desktop:[2015.xlsx]Input'!$F:$F,
'Macintosh HD: Desktop:[2015.xlsx]Input'!$B:$B,$D3,
'Macintosh HD: Desktop:[2015.xlsx]Input'!$G:$G,
F$1)

In the same 2016 workbook in cell H3 I have:
=SUMIFS
('Macintosh HD: Desktop:[2015.xlsx]Input'!$F:$F,
'Macintosh HD: Desktop:[2015.xlsx]Input'!$B:$B,$D3,
'Macintosh HD: Desktop:[2015.xlsx]Input'!$G:$G,
H$1)

Can anyone help me to either fix the formula or recommend an alternative formula to avoid the #VALUE! message or to have the linked workbook also opened?

Thank you in advance, Gijs

SUMIFS linked to other workbook, returns #VALUE!
 

Hi,

Am Sun, 8 May 2016 12:17:04 +0100 schrieb GijsKijlstra:

Besides 2016 data in my 2016 workbook, I have there 2015 data for
comparison purposes, with SUMIFS formulas linked to my 2015 workbook.
The 2016 workbook presents the correct 2016 values but return an #VALUE!
message for the 2015 values, when I open the 2016 workbook and the 2015
workbook is closed. (Clicking “ignore links” or “Update” doesn’t solve
the issue). Only when I click “Edit Link” and “Open Source” –
effectively opening the 2015 workbook - I get the correct (2015) data.

use SUMPRODUCT to refer to a closed workbook.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thank you Claus, for your fast response.

Not familiar with the SUMPRODUCT formula I looked it up, they appeared identical to the SUMIFS formula and changed them as follows:
= SUMPRODUCT
('Macintosh HD:Desktop:[2015.xlsx]Input'!$F:$F,
'Macintosh HD: Desktop:[2015.xlsx]Input'!$B:$B,$D3,
'Macintosh HD: Desktop:[2015.xlsx]Input'!$G:$G,
F$1)

Regrettably I still have an #VALUE! message. Can you tell me what I’ve done wrong?