# Re: SUMIF from multiple files

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 here:
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
.