SUMIF from multiple files

U

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

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

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.

Dave said:
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?
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top