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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
>
>