software

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

quot;Dave Petersonquot; wrote:

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

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
xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

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