# Re: SUMIF from multiple files

*From*: Dave Peterson <petersod@xxxxxxxxxxxxxxxx>*Date*: Mon, 21 Apr 2008 06:47:13 -0500

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

.

**Follow-Ups**:**Re: SUMIF from multiple files***From:*Dave Peterson

**References**:**SUMIF from multiple files***From:*Unda

- Prev by Date:
**RE: Custom Format with different Criteria** - Next by Date:
**Re: SUMIF from multiple files** - Previous by thread:
**SUMIF from multiple files** - Next by thread:
**Re: SUMIF from multiple files** - Index(es):