#VALUE error on SUMIF formula linked to multiple worksheets

[bknutzy]

I am receiving a #VALUE error on a SUMIF formula that I have linked to an
external spreadsheet. If I only have the spreadsheet with the SUMIF formula
open, I receive the #VALUE error. But if I open the external spreadsheet
that it is linked to, then the #VALUE error disappears and the correct value
displays.

Would anyone know why this is happening? I would prefer to only have to
open my one spreadsheet.
Any suggestions would be great.

Thanks,

 

[Dave Peterson]

There are some functions that will only work if the sending workbook is open.

=countif(), =sumif(), =indirect()

are a few.

But there can be replacement formulas that may work for you:
=sumproduct()

If you can get your formula to work when the sending workbook is open, post that
working formula and maybe someone can give you an alternative.

To get you started:

=SUMPRODUCT(--('C:\My Documents\Excel\[book1.xls]Sheet1'!$A$1:$A$10="asdf"),
'C:\My Documents\Excel\[book1.xls]Sheet1'!$B$1:$B$10)

is the equivalent of:

=SUMif('C:\My Documents\Excel\[book1.xls]Sheet1'!$A$1:$A$10,"asdf",
'C:\My Documents\Excel\[book1.xls]Sheet1'!$B$1:$B$10)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html