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?

Use SUMPRODUCT.

--
Biff
Microsoft Excel MVP


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

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

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

GB

"T. Valko" wrote:

Use SUMPRODUCT.

--
Biff
Microsoft Excel MVP


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

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



GB wrote:

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

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

GB

"T. Valko" wrote:

Use SUMPRODUCT.

--
Biff
Microsoft Excel MVP


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




--

Dave Peterson

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" wrote:

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



GB wrote:

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

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

GB

"T. Valko" wrote:

Use SUMPRODUCT.

--
Biff
Microsoft Excel MVP


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




--

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

GB wrote:

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" wrote:

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



GB wrote:

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

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

GB

"T. Valko" wrote:

Use SUMPRODUCT.

--
Biff
Microsoft Excel MVP


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




--

Dave Peterson


--

Dave Peterson