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

Haytham Amairah

Silver Contributor

Mar 19, 2018

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

arshadtape

Copper Contributor

Feb 17, 2022

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?

ExcelLearnEasy
Copper Contributor
Jun 23, 2022
arshadtape I came across the same issue but I Used Index Match Formula to get results that I want from the closed external workbook.

Forum Discussion

Sumifs using external links returning #VALUE unless source file open

Resources