Forum Discussion
Sumifs using external links returning #VALUE unless source file open
Hello Marcel,
SUMIFS function in addition to SUMIF, COUNTIF and COUNTIFS require the destination workbook to be open, to keep calculating the result!
Otherwise, it will return #VALUE! error.
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!
The solution is to use the alternative and the most powerful function (SUMPRODUCT) as the following syntax:
=SUMPRODUCT(--(criteria_range1 = criteria1), --(criteria_range2 = criteria2) ... , sum_range)
Same issue https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_365hp/excel-function-sumifsumifs/214794b4-a50e-4d11-8a96-be6e36c40870.
Hope that helps
Haytham
hello, i have the same problem with countifs. is there a solution to have connections updated without opening source file?
thanks
Andrea
Andrea, you may use SUPRODUCT() as well, just exclude sum_range in previous example
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.
I meant Haytham Amairah post.
With COUNTIFS the syntax is COUNIFS(range1, criteria1, range2, criteria2,..). With SUMPRODUCT the same will be like SUMPRODUCT( ( range1= criteria1)*(range2=criteria2)*...)
