Forum Discussion

Copper Contributor

Mar 19, 2018

Sumifs using external links returning #VALUE unless source file open

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

Formulas & Functions

Need Help

Valente72

Copper Contributor

Jan 22, 2020

Haytham Amairah

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

thanks

Andrea

SergeiBaklan

Diamond Contributor

Jan 22, 2020

Valente72

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

Valente72
Copper Contributor
Jan 22, 2020
SergeiBaklan
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.
- SergeiBaklan
  Diamond Contributor
  Jan 22, 2020
  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)*...)