SUMIF Returns a #VALUE error when external source is closed

  • Thread starter Thread starter ghynes
  • Start date Start date
G

ghynes

Can you help me out with this? if works fine if the external source i
open but returns a #value if its closed. is there anyway of correctin
this so that it will pull from the external when it is closed
 
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.
 
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel #Value! Error on external link, when source file closed 3
Sumif formula returning #VALUE when source worksheet is not open 4
SUMIF returning "#VALUE!" for external link 3
#Value w external links 2
Sumif to external file 1
Excel sum if and closed workbooks 2
Error when Formula references a pivot table in an external workboo 2
Linking formula to external spreadsheet 1

Back
Top