SUMIF - External Workbooks

[splash]

Just a quick question, folks.

I have a SUMIFformula, the two arguments (range and sumrange) being two different columns on the same workbook, on a different network drive to my spreadsheet (saved on C).

When I have the external spreadsheet open, the results appear no problem, but whenever I close the external book (on the network) the return value is #VALUE!

Is it impossible to SUMIF from external workbooks?

My formula:

S:\Test_Network\2009\P12_Dec\[MILAN_7247.xls]RAR1_Wk4'!$A$1:$A$100,"Test Cristeria",S:\Test_Network\2009\P12_Dec\[MILAN_7247.xls]RAR1_Wk4'!$B$1:$B$100

Thanks in advance.
Splash.

 

[sanrv1f]

switch to SUMPRODUCT, like

=SUMPRODUCT(([B]S:\Test_Network\2009\P12_Dec\[MILAN_7247.xls]RAR1_Wk4'!$A$1:$A$100="Test Cristeria"),S:\Test_Network\2009\P12_Dec\[MILAN_7247.xls]RAR1_Wk4'!$B$1:$B$100)[/B]
[B]

[/B]

 

[xld]

SUMIF doesn't work with a closed workbook, try SUMPRODUCT

=SUMPRODUCT((S:\Test_Network\2009\P12_Dec\[MILAN_7247.xls]RAR1_Wk4'!$A$1:$A$100="Test Cristeria")*(S:\Test_Network\2009\P12_Dec\[MILAN_7247.xls]RAR1_Wk4'!$B$1:$B$100))

 

[splash]

Appreciate the help thus far.

I’ll report back once done.

 

[splash]

Here's my new formula:
=SUMPRODUCT(([BR_P7W5.xls]WRS1_Wk5!$A:$A="3663EI")*[BR_P7W5.xls]WRS1_Wk5!I:I)

Now it's returning a #NUM! message rather than a true value.

Any ideas?

Thanks a lot

 

[sanrv1f]

you can't use whole column reference in SUMPRODUCT,

try

=SUMPRODUCT(--([BR_P7W5.xls]WRS1_Wk5!$A1:$A65535="3663EI"),[BR_P7W5.xls]WRS1_Wk5!I1:I65535)

 

[splash]

Thanks ever so much - that's worked tremendously.