Sumifs using external links returning #VALUE unless source file open

Question

I am using the multiple criteria sumifs on external workbooks and the result is #VALUE unless I have the source file open.&nbsp; I have tried refreshing the data to no effect, it seems to require the file to be open to access it.

haytham amairah · Answer

Hello Marcel,
&nbsp;
SUMIFS function in addition to SUMIF, COUNTIF and COUNTIFS require the destination workbook to be open, to keep calculating the result!
&nbsp;
Otherwise, it will return #VALUE! error.&nbsp;
The reason is that the range in the function turns into a full path when you close the workbook, and since these functions are lightweight functions they cannot handle this full path so it treats it as an array!
&nbsp;
The solution is to use the alternative and the most powerful function (SUMPRODUCT) as the following syntax:
&nbsp;
=SUMPRODUCT(--(criteria_range1 = criteria1), --(criteria_range2 = criteria2) ... , sum_range)&nbsp;
&nbsp;
Same issue https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_365hp/excel-function-sumifsumifs/214794b4-a50e-4d11-8a96-be6e36c40870.
&nbsp;
Hope that helps
Haytham
&nbsp;

valente72 · Answer

Haytham Amairah&nbsp;hello, i have the same problem with countifs. is there a solution to have connections updated without opening source file?thanksAndrea

sergeibaklan · Answer

Valente72&nbsp;
Andrea, you may use SUPRODUCT() as well, just exclude sum_range in previous example

valente72 · Answer

SergeiBaklan&nbsp;hello sergei, thanks for your quick answer but i can't succeed!! in my excel 2016, sumproduct syntax is: sumproduct(matrix1, matrix2, matrix 3,,,,,), i can't see criteria or sum range.&nbsp;

sergeibaklan · Answer

Valente72&nbsp;
I meant&nbsp;Haytham Amairah&nbsp; post.&nbsp;
With COUNTIFS the syntax is COUNIFS(range1, criteria1, range2, criteria2,..). With SUMPRODUCT the same will be like SUMPRODUCT( (&nbsp;range1= criteria1)*(range2=criteria2)*...)

Forum Discussion

Sumifs using external links returning #VALUE unless source file open

7 Replies