SUMIF from multiple files

[Unda]

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]

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

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]

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.

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

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?

 

[David Biddulph]

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.