My sumif formulas containin links to other workbooks do not calcul

[Guest]

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]

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.

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

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.

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?

 

[Guest]

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.

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

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.

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?

 

[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

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.

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