There are some functions that won't work unless the sending file is open.
=sumif(), =countif(), =indirect()
are a few.

You could replace the formula with the equivalent =sumproduct()

=SUMproduct(--([01.xls]Sheet1!$A$5:$A$1000=A8);
[01.xls]Sheet1!F$5:F$1000)+
......

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

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


Unda wrote:

I'm trying to have a sum with conditions from multiple files, like

=SUMIF([01.xls]Sheet1!$A$5:$A$1000;A8;[01.xls]Sheet1!F$5:F$1000)+
SUMIF([02.xls]Sheet1'!$A$5:$A$1000;A8;[02.xls]Sheet1'!F$5:F$1000)+
SUMIF([03.xls]Sheet1'!$A$5:$A$1000;A8;[03.xls]Sheet1'!F$5:F$1000)+
SUMIF([04.xls]Sheet1'!$A$5:$A$1000;A8;[04.xls]Sheet1'!F$5:F$1000)+ ...

but all I get is #### (Error in value). Any clue what's wrong and how can I
get this to work?

--

Dave Peterson