My sumif formulas containin links to other workbooks do not calcul

S

sgm_wfa

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?
 
D

Dave Peterson

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 said:
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?
 
D

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 said:
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 said:
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?
 
S

sgm_wfa

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 said:
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 said:
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 said:
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?
 
R

Roger Govier

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 said:
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 said:
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.)
 

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

Top