SUMIF function #VALUE! error links

[Guest]

I'm trying to perform a SUMIF function in one workbook (destination) using
some cells in another workbook (source). It only works if I have the source
workbook open. If it's not open, I get a #VALUE! error in the SUMIF cell of
the destination workbook. Any suggestions?

 

[T. Valko]

Use SUMPRODUCT.

 

[Guest]

Thanks. How do I convert the folling SUMIF to a SUMPRODUCT?

=SUMIF('SourceFile'!$D$9:$D$5000,$B9,'SourceFile'!$G$9$G5000)

GB

 

[Dave Peterson]

=SUMproduct(--('SourceFile'!$D$9:$D$5000=$B9),'SourceFile'!$G$9$G5000)

But this points at another worksheet in the same workbook--not a different
worksheet in another workbook.

 

[Guest]

Dave, thanks. That worked perfectly. On your pointing statement, Cell $B9
(in the below formula) is in the "destination file", while Cells $D$9:$D$5000
and $G$9:$G5000 are in the "source file". Sorry for any confusions.

Finally, what's the purpose of the "--" at the beginning of the SUMPRODUCT
function? Just curious.

Thanks again,

GB

 

[Dave Peterson]

=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