My sumif formulas containin links to other workbooks do not calcul

My sumif formulas containin links to other workbooks do not calculate. Once
I close the source workbook, the formula results turn to "#value!" The
source workbook is listed under links in the destination workbook. I have
other links that are not in a formula and they are updating correctly. The
source workbook is called Test and and the data is on sheet1. Here is the
formula in my destination workbook: =SUMIF('C:\Documents and
Settings\Grant\My Documents\[Test.xls]Sheet1'!$A:$A,A3,'C:\Documents and
Settings\Grant\My Documents\[Test.xls]Sheet1'!$B:$B)

Any suggestions?

There are some worksheet functions that don't work with closed workbooks.

=indirect(), =sumif(), =countif() are a few.

But maybe you could use a different formula:

=SUMproduct(--('C:\yourfolder\[Test.xls]Sheet1'!$A1:$A99,A3),
('C:\yourfolder\[Test.xls]Sheet1'!$B1:$B99))

Adjust the range to match--but you can't use the whole column.

And I changed the folder name to shorten the formula for this post.

If you create the formula with the test.xls workbook open, you may find it
easier. Excel will adjust the formula when you close that workbook.

sgm_wfa wrote:
>
> My sumif formulas containin links to other workbooks do not calculate. Once
> I close the source workbook, the formula results turn to "#value!" The
> source workbook is listed under links in the destination workbook. I have
> other links that are not in a formula and they are updating correctly. The
> source workbook is called Test and and the data is on sheet1. Here is the
> formula in my destination workbook: =SUMIF('C:\Documents and
> Settings\Grant\My Documents\[Test.xls]Sheet1'!$A:$A,A3,'C:\Documents and
> Settings\Grant\My Documents\[Test.xls]Sheet1'!$B:$B)
>
> Any suggestions?

--

Dave Peterson

typo alert...

=SUMproduct(--('C:\yourfolder\[Test.xls]Sheet1'!$A1:$A99,A3),
('C:\yourfolder\[Test.xls]Sheet1'!$B1:$B99))

Should have been:

=SUMproduct(--('C:\yourfolder\[Test.xls]Sheet1'!$A1:$A99=A3),
('C:\yourfolder\[Test.xls]Sheet1'!$B1:$B99))

(The comma before A3 should have been an equal sign.)

Dave Peterson wrote:
>
> There are some worksheet functions that don't work with closed workbooks.
>
> =indirect(), =sumif(), =countif() are a few.
>
> But maybe you could use a different formula:
>
> =SUMproduct(--('C:\yourfolder\[Test.xls]Sheet1'!$A1:$A99,A3),
> ('C:\yourfolder\[Test.xls]Sheet1'!$B1:$B99))
>
> Adjust the range to match--but you can't use the whole column.
>
> And I changed the folder name to shorten the formula for this post.
>
> If you create the formula with the test.xls workbook open, you may find it
> easier. Excel will adjust the formula when you close that workbook.
>
> sgm_wfa wrote:
> >
> > My sumif formulas containin links to other workbooks do not calculate. Once
> > I close the source workbook, the formula results turn to "#value!" The
> > source workbook is listed under links in the destination workbook. I have
> > other links that are not in a formula and they are updating correctly. The
> > source workbook is called Test and and the data is on sheet1. Here is the
> > formula in my destination workbook: =SUMIF('C:\Documents and
> > Settings\Grant\My Documents\[Test.xls]Sheet1'!$A:$A,A3,'C:\Documents and
> > Settings\Grant\My Documents\[Test.xls]Sheet1'!$B:$B)
> >
> > Any suggestions?
>
> --
>
> Dave Peterson

--

Dave Peterson

Dave,
Thank you for your answer. The formula works perfectly now. I looked
at the description of sumproduct in excel help and it doesn't describe it as
working like a sumif formula. I see that you added the "--" at the beginning
and used =A3 instead of ,A3. Can you tell me how this works? (If this is a
stupid question, please feel free to not reply to it but I am just curious to
know how the formula works)

Thanks again.

"Dave Peterson" wrote:

> typo alert...
>
> =SUMproduct(--('C:\yourfolder\[Test.xls]Sheet1'!$A1:$A99,A3),
> ('C:\yourfolder\[Test.xls]Sheet1'!$B1:$B99))
>
> Should have been:
>
> =SUMproduct(--('C:\yourfolder\[Test.xls]Sheet1'!$A1:$A99=A3),
> ('C:\yourfolder\[Test.xls]Sheet1'!$B1:$B99))
>
> (The comma before A3 should have been an equal sign.)
>
> Dave Peterson wrote:
> >
> > There are some worksheet functions that don't work with closed workbooks.
> >
> > =indirect(), =sumif(), =countif() are a few.
> >
> > But maybe you could use a different formula:
> >
> > =SUMproduct(--('C:\yourfolder\[Test.xls]Sheet1'!$A1:$A99,A3),
> > ('C:\yourfolder\[Test.xls]Sheet1'!$B1:$B99))
> >
> > Adjust the range to match--but you can't use the whole column.
> >
> > And I changed the folder name to shorten the formula for this post.
> >
> > If you create the formula with the test.xls workbook open, you may find it
> > easier. Excel will adjust the formula when you close that workbook.
> >
> > sgm_wfa wrote:
> > >
> > > My sumif formulas containin links to other workbooks do not calculate. Once
> > > I close the source workbook, the formula results turn to "#value!" The
> > > source workbook is listed under links in the destination workbook. I have
> > > other links that are not in a formula and they are updating correctly. The
> > > source workbook is called Test and and the data is on sheet1. Here is the
> > > formula in my destination workbook: =SUMIF('C:\Documents and
> > > Settings\Grant\My Documents\[Test.xls]Sheet1'!$A:$A,A3,'C:\Documents and
> > > Settings\Grant\My Documents\[Test.xls]Sheet1'!$B:$B)
> > >
> > > Any suggestions?
> >
> > --
> >
> > Dave Peterson
>
> --
>
> Dave Peterson
>

Hi

The double unary minus -- is used to coerce the values of True to 1 and
False to 0 so that they can be used in the calculation within
Sumproduct.

For an excellent treatise on this and the Sumproduct function in
general, take a look at Bob Phillip's site
http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


sgm_wfa <sgmwfa@discussions.microsoft.com> wrote
> Dave,
> Thank you for your answer. The formula works perfectly now. I
> looked at the description of sumproduct in excel help and it
> doesn't describe it as working like a sumif formula. I see that
> you added the "--" at the beginning and used =A3 instead of ,A3.
> Can you tell me how this works? (If this is a stupid question,
> please feel free to not reply to it but I am just curious to know
> how the formula works) Thanks again.
> "Dave Peterson" wrote:
>> typo alert...
>> =SUMproduct(--('C:\yourfolder\[Test.xls]Sheet1'!$A1:$A99,A3),
>> ('C:\yourfolder\[Test.xls]Sheet1'!$B1:$B99))
>> Should have been:
>> =SUMproduct(--('C:\yourfolder\[Test.xls]Sheet1'!$A1:$A99=A3),
>> ('C:\yourfolder\[Test.xls]Sheet1'!$B1:$B99))
>> (The comma before A3 should have been an equal sign.)