Sumifs using external links returning #VALUE unless source file open

Copper Contributor

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.

7 Replies

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

 

@Haytham Amairah 

hello, i have the same problem with countifs. is there a solution to have connections updated without opening source file?

thanks

Andrea

@Valente72 

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

@Sergei Baklan 

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.

 

@Valente72 

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)*...)

@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?

 

@arshadtape I came across the same issue but I Used Index Match Formula to get results that I want from the closed external workbook.