SUMIF Returns a #VALUE error when external source is closed
 

When I am using multiple workbooks, and have data in one book, and a sumif
function in the other I am receiving an error. When the data source is open
everything works fine, but should I close the external data source then re
open and update the workbook which has the SUMIF function then I receive a
#VALUE error.

=sumif([Workbook.xls]Sheet1'!$A$1:$A$3,=1,[Workbook.xls]Sheet1'!$B$1:B$3)

I would appreciate any suggestions on this matter.

First of all your formula as presented wouldn't work even if the other
workbook is open

=sumif(range1,1,range2)

or

=sumif(range1,"=1",range2)

and not

=sumif(range1=1,range2)

Use sumproduct instead

=SUMPRODUCT(--([Workbook.xls]Sheet1!$A$1:$A$3=1),[Workbook.xls]Sheet1!$B$1:$B$3)

will work on closed workbooks

--
Regards,

Peo Sjoblom


"Chad" wrote in message
...
When I am using multiple workbooks, and have data in one book, and a sumif
function in the other I am receiving an error. When the data source is
open
everything works fine, but should I close the external data source then re
open and update the workbook which has the SUMIF function then I receive a
#VALUE error.

=sumif([Workbook.xls]Sheet1'!$A$1:$A$3,=1,[Workbook.xls]Sheet1'!$B$1:B$3)

I would appreciate any suggestions on this matter.