SUMIF from multiple files
 

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?

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

SUMIF from multiple files
 

ps. Build the formula with the sending workbooks open.

It'll be easier to debug. When you close those sending files, excel will adjust
the formula to include the paths.

Dave Peterson wrote:

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

--

Dave Peterson

SUMIF from multiple files
 

Ttry widening your column, or reducing your font size. Then try breaking up
your formula into manageable chunks, and see what you get from each part,
such as =SUMIF([01.xls]Sheet1!$A$5:$A$1000;A8;[01.xls]Sheet1!F$5:F$1000),
and then if you are still getting problems, look at the source data.

I would expect you to get a #VALUE! error if the other file isn't open.
--
David Biddulph

"Unda" wrote in message
...
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?