Mar 19 2018
- last edited on
Jul 25 2018
I am using the multiple criteria sumifs on external workbooks and the result is #VALUE unless I have the source file open. I have tried refreshing the data to no effect, it seems to require the file to be open to access it.
Mar 19 2018 07:58 AM
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 here.
Hope that helps
Jan 22 2020 01:08 AM
hello, i have the same problem with countifs. is there a solution to have connections updated without opening source file?
Jan 22 2020 04:02 AM
Andrea, you may use SUPRODUCT() as well, just exclude sum_range in previous example
Jan 22 2020 05:32 AM
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.
Jan 22 2020 05:46 AM