SUMIF Returns a #VALUE error when external source is closed
 

Can you help me out with this? if works fine if the external source is
open but returns a #value if its closed. is there anyway of correcting
this so that it will pull from the external when it is closed?


--
ghynes
------------------------------------------------------------------------
ghynes's Profile: http://www.excelforum.com/member.php...o&userid=26587
View this thread: http://www.excelforum.com/showthread...hreadid=398561

No, the formula requires the second file to be open.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"ghynes" wrote in
message ...

Can you help me out with this? if works fine if the external source is
open but returns a #value if its closed. is there anyway of correcting
this so that it will pull from the external when it is closed?


--
ghynes
------------------------------------------------------------------------
ghynes's Profile:
http://www.excelforum.com/member.php...o&userid=26587
View this thread: http://www.excelforum.com/showthread...hreadid=398561

You could use a different function:

=sumproduct() will work with closed workbooks:

=SUMPRODUCT(--('[book1.xls]sheet1'!$A$1:$A$10=A1),
'[book1.xls]sheet1'!$b$1:$b10)

As an example.



ghynes wrote:

Can you help me out with this? if works fine if the external source is
open but returns a #value if its closed. is there anyway of correcting
this so that it will pull from the external when it is closed?

--
ghynes
------------------------------------------------------------------------
ghynes's Profile: http://www.excelforum.com/member.php...o&userid=26587
View this thread: http://www.excelforum.com/showthread...hreadid=398561

--

Dave Peterson

Try SUMPRODUCT instead...

=SUMPRODUCT(--('C:\Path\[FileName.xls]Sheet1'!$A$1:$A$100="Criteria"),'C:\Path\[FileName.xls]Sheet1'!$B$1:$B$100)

Note that if the criteria is a numerical value, remove the quotes.

Hope this helps!

ghynes Wrote:
Can you help me out with this? if works fine if the external source is
open but returns a #value if its closed. is there anyway of correcting
this so that it will pull from the external when it is closed?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=398561

nice one. that works. thanks.


--
ghynes
------------------------------------------------------------------------
ghynes's Profile: http://www.excelforum.com/member.php...o&userid=26587
View this thread: http://www.excelforum.com/showthread...hreadid=398561

that works. cool. thanks.