SUMIF returning "#VALUE!" for external link

[TDietrich]

Hello,

I am trying to use the SUMIF function to sum information
from an external sourse. This is the function:

=SUMIF(range to check for the criteria, criteria, range to
sum)

Here is my formula:

=SUMIF($B$8:$B$800,1,'C:\MyDocs\[2004-09forTim.xls]Sheet1'!
$C$8:$C$800)

The range to check is internal however the range to sum is
linked to an external file and needs to remain that way.
This formula DOES work when the external file is open,
however when I close the external file the formula value
becomes "#VALUE!". I am trying to set this up so that I
do not have to open the external file for this formula to
work.

Any suggestions?

 

[Guest]

Hi SUMIF can't work with closed files. BUT you can replace it with a
SUMPRODUCT formula. Try:

SUMPRODUCT(--($B$8:$B$800=1),'C:\MyDocs\[2004-09forTim.xls]Sheet1'!
$C$8:$C$800)

I'm just wondering if your first range is correct and should not also refer
to the other workbook?

 

[TDietrich]

That worked PERFECT. THANK YOU!!

The external sheets are created by another company. The
first range is data not on there sheet that I have to add
based off of their sheets.

Thanks again!

-----Original Message-----
Hi SUMIF can't work with closed files. BUT you can replace it with a
SUMPRODUCT formula. Try:

SUMPRODUCT(--($B$8:$B$800=1),'C:\MyDocs\[2004- 09forTim.xls]Sheet1'!
$C$8:$C$800)

I'm just wondering if your first range is correct and should not also refer
to the other workbook?

Hello,

I am trying to use the SUMIF function to sum information
from an external sourse. This is the function:

=SUMIF(range to check for the criteria, criteria, range to
sum)

Here is my formula:

=SUMIF($B$8:$B$800,1,'C:\MyDocs\[2004-09forTim.xls] Sheet1'!
$C$8:$C$800)

The range to check is internal however the range to sum is
linked to an external file and needs to remain that way.
This formula DOES work when the external file is open,
however when I close the external file the formula value
becomes "#VALUE!". I am trying to set this up so that I
do not have to open the external file for this formula to
work.

Any suggestions?

.

 

[Tim]

Quck question, what does the -- do in the formula below

SUMPRODUCT(--(

Thanks

-----Original Message-----
That worked PERFECT. THANK YOU!!

The external sheets are created by another company. The
first range is data not on there sheet that I have to add
based off of their sheets.

Thanks again!

-----Original Message-----
Hi SUMIF can't work with closed files. BUT you can replace it with a
SUMPRODUCT formula. Try:

SUMPRODUCT(--($B$8:$B$800=1),'C:\MyDocs\[2004- 09forTim.xls]Sheet1'!
$C$8:$C$800)

I'm just wondering if your first range is correct and should not also refer
to the other workbook?

Hello,

I am trying to use the SUMIF function to sum information
from an external sourse. This is the function:

=SUMIF(range to check for the criteria, criteria,

range

to

sum)

Here is my formula:

=SUMIF($B$8:$B$800,1,'C:\MyDocs\[2004-09forTim.xls] Sheet1'!
$C$8:$C$800)

The range to check is internal however the range to

sum

is
.

.