Mar 19 2018
07:35 AM
- last edited on
Jul 25 2018
11:24 AM
by
TechCommunityAP
Mar 19 2018
07:35 AM
- last edited on
Jul 25 2018
11:24 AM
by
TechCommunityAP
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
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 here.
Hope that helps
Haytham
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?
thanks
Andrea
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
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)*...)
Feb 17 2022 03:11 AM
@Haytham Amairah Hi Haytham. Ive tried using SUMPROD but still no success. the sum_range i have included last (array 3). is there something that i am missing?
Jun 23 2022 10:44 AM