#Value! error on links

[GazNicki]

Hi

I have a worksheet that is looking up the values of cell A4 in an external workbook, and adding together all the instances of the search. Here is the code inside one of the cells.

=SUMIF('G:\ORDER CONTROLS\ORDER SUMMARY\2018\05 May 2018\[WE 12 MAY 2018 ORDER SUMMARY.xlsx]ORDER'!$A$5:$A$81,$A6,'G:\ORDER CONTROLS\ORDER SUMMARY\2018\05 May 2018\[WE 12 MAY 2018 ORDER SUMMARY.xlsx]ORDER'!$K$5:$K$81)

If I have the source workbook open, all is well and the sheet works fine.

If I have the source workbook closed, then I am greeted with #Value ! errors in every cell.

As you can see, I have the full source location in the equation, but something is amiss.

Can anyone please help me?

 

[GazNicki]

I managed to fix this.

Turns out this is a common error for SUMIF functions, however if I converted the SUM if to a SUM and and IF statement, and submitted as an array (CTRL+SHIFT+ENTER) it works.

Here is the revised code for anyone interested:

=SUM(IF('G:\ORDER CONTROLS\ORDER SUMMARY\2018\05 May 2018\[WE 12 MAY 2018 ORDER SUMMARY.xlsx]ORDER'!$A$5:$A$81=$A5,'G:\ORDER CONTROLS\ORDER SUMMARY\2018\05 May 2018\[WE 12 MAY 2018 ORDER SUMMARY.xlsx]ORDER'!$K$5:$K$81,0))

You MUST submit this formula by pressing CTRL+SHIFT+ENTER to make it work, else it will just sum everything in the last part of the formula.