I am working with 2 worksheets and cannot get the sumproduct to work. I need
to sum quantities from 'Production' worksheet to 'Inventory' worksheet, where
criteria in 3 columns of 'Production' matches criteria from 'Inventory'. My
formula below is giving me a '0' quantity instead of the actual total of
'1440'. Any idea where I am going wrong?

=SUMPRODUCT(('[Production.xls]Sheet1'!$B$2:$B$2139=B9)*('[Production.xls]Sheet1'!$G$2:$G$2139=X9)*('[Production.xls]Sheet1'!$I$2:$I$2139=D9)*('[Production.xls]Sheet1'!$D$2:$D$2139))

Try

=SUMPRODUCT(
('[Production.xls]Sheet1'!$B$2:$B$2139=B9)*
('[Production.xls]Sheet1'!$G$2:$G$2139=X9)*
('[Production.xls]Sheet1'!$I$2:$I$2139=D9),
('[Production.xls]Sheet1'!$D$2:$D$2139))

If this post helps click Yes
---------------
Jacob Skaria


"brownmre" wrote:

I am working with 2 worksheets and cannot get the sumproduct to work. I need
to sum quantities from 'Production' worksheet to 'Inventory' worksheet, where
criteria in 3 columns of 'Production' matches criteria from 'Inventory'. My
formula below is giving me a '0' quantity instead of the actual total of
'1440'. Any idea where I am going wrong?

=SUMPRODUCT(('[Production.xls]Sheet1'!$B$2:$B$2139=B9)*('[Production.xls]Sheet1'!$G$2:$G$2139=X9)*('[Production.xls]Sheet1'!$I$2:$I$2139=D9)*('[Production.xls]Sheet1'!$D$2:$D$2139))

Hi,
try

..=SUMPRODUCT(--('[Production.xls]Sheet1'!$B$2:$B$2139=B9),--('[Production.xls]Sheet1'!$G$2:$G$2139=X9),--('[Production.xls]Sheet1'!$I$2:$I$2139=D9),'[Production.xls]Sheet1'!$D$2:$D$2139)

"brownmre" wrote:

I am working with 2 worksheets and cannot get the sumproduct to work. I need
to sum quantities from 'Production' worksheet to 'Inventory' worksheet, where
criteria in 3 columns of 'Production' matches criteria from 'Inventory'. My
formula below is giving me a '0' quantity instead of the actual total of
'1440'. Any idea where I am going wrong?

=SUMPRODUCT(('[Production.xls]Sheet1'!$B$2:$B$2139=B9)*('[Production.xls]Sheet1'!$G$2:$G$2139=X9)*('[Production.xls]Sheet1'!$I$2:$I$2139=D9)*('[Production.xls]Sheet1'!$D$2:$D$2139))

I couldn't get either to work. Does it matter if both spreadsheets are open?
Also I forgot to mention that I'm using Excel 2003.

"Eduardo" wrote:

Hi,
try

.=SUMPRODUCT(--('[Production.xls]Sheet1'!$B$2:$B$2139=B9),--('[Production.xls]Sheet1'!$G$2:$G$2139=X9),--('[Production.xls]Sheet1'!$I$2:$I$2139=D9),'[Production.xls]Sheet1'!$D$2:$D$2139)

"brownmre" wrote:

I am working with 2 worksheets and cannot get the sumproduct to work. I need
to sum quantities from 'Production' worksheet to 'Inventory' worksheet, where
criteria in 3 columns of 'Production' matches criteria from 'Inventory'. My
formula below is giving me a '0' quantity instead of the actual total of
'1440'. Any idea where I am going wrong?

=SUMPRODUCT(('[Production.xls]Sheet1'!$B$2:$B$2139=B9)*('[Production.xls]Sheet1'!$G$2:$G$2139=X9)*('[Production.xls]Sheet1'!$I$2:$I$2139=D9)*('[Production.xls]Sheet1'!$D$2:$D$2139))

Try with some dummy data with 1 criteria in the same sheet first and then
build on that..

If this post helps click Yes
---------------
Jacob Skaria


"brownmre" wrote:

I couldn't get either to work. Does it matter if both spreadsheets are open?
Also I forgot to mention that I'm using Excel 2003.

"Eduardo" wrote:

Hi,
try

.=SUMPRODUCT(--('[Production.xls]Sheet1'!$B$2:$B$2139=B9),--('[Production.xls]Sheet1'!$G$2:$G$2139=X9),--('[Production.xls]Sheet1'!$I$2:$I$2139=D9),'[Production.xls]Sheet1'!$D$2:$D$2139)

"brownmre" wrote:

I am working with 2 worksheets and cannot get the sumproduct to work. I need
to sum quantities from 'Production' worksheet to 'Inventory' worksheet, where
criteria in 3 columns of 'Production' matches criteria from 'Inventory'. My
formula below is giving me a '0' quantity instead of the actual total of
'1440'. Any idea where I am going wrong?

=SUMPRODUCT(('[Production.xls]Sheet1'!$B$2:$B$2139=B9)*('[Production.xls]Sheet1'!$G$2:$G$2139=X9)*('[Production.xls]Sheet1'!$I$2:$I$2139=D9)*('[Production.xls]Sheet1'!$D$2:$D$2139))

Hi,
In my post there was an extra dot at the beginning of the formula, did you
delete it?, if yes please tell me which results or error message do you get

"Jacob Skaria" wrote:

Try with some dummy data with 1 criteria in the same sheet first and then
build on that..

If this post helps click Yes
---------------
Jacob Skaria


"brownmre" wrote:

I couldn't get either to work. Does it matter if both spreadsheets are open?
Also I forgot to mention that I'm using Excel 2003.

"Eduardo" wrote:

Hi,
try

.=SUMPRODUCT(--('[Production.xls]Sheet1'!$B$2:$B$2139=B9),--('[Production.xls]Sheet1'!$G$2:$G$2139=X9),--('[Production.xls]Sheet1'!$I$2:$I$2139=D9),'[Production.xls]Sheet1'!$D$2:$D$2139)

"brownmre" wrote:

I am working with 2 worksheets and cannot get the sumproduct to work. I need
to sum quantities from 'Production' worksheet to 'Inventory' worksheet, where
criteria in 3 columns of 'Production' matches criteria from 'Inventory'. My
formula below is giving me a '0' quantity instead of the actual total of
'1440'. Any idea where I am going wrong?

=SUMPRODUCT(('[Production.xls]Sheet1'!$B$2:$B$2139=B9)*('[Production.xls]Sheet1'!$G$2:$G$2139=X9)*('[Production.xls]Sheet1'!$I$2:$I$2139=D9)*('[Production.xls]Sheet1'!$D$2:$D$2139))

Thanks, that worked. My 3rd criteria was causing the problem.



"Jacob Skaria" wrote:

Try with some dummy data with 1 criteria in the same sheet first and then
build on that..

If this post helps click Yes
---------------
Jacob Skaria


"brownmre" wrote:

I couldn't get either to work. Does it matter if both spreadsheets are open?
Also I forgot to mention that I'm using Excel 2003.

"Eduardo" wrote:

Hi,
try

.=SUMPRODUCT(--('[Production.xls]Sheet1'!$B$2:$B$2139=B9),--('[Production.xls]Sheet1'!$G$2:$G$2139=X9),--('[Production.xls]Sheet1'!$I$2:$I$2139=D9),'[Production.xls]Sheet1'!$D$2:$D$2139)

"brownmre" wrote:

I am working with 2 worksheets and cannot get the sumproduct to work. I need
to sum quantities from 'Production' worksheet to 'Inventory' worksheet, where
criteria in 3 columns of 'Production' matches criteria from 'Inventory'. My
formula below is giving me a '0' quantity instead of the actual total of
'1440'. Any idea where I am going wrong?

=SUMPRODUCT(('[Production.xls]Sheet1'!$B$2:$B$2139=B9)*('[Production.xls]Sheet1'!$G$2:$G$2139=X9)*('[Production.xls]Sheet1'!$I$2:$I$2139=D9)*('[Production.xls]Sheet1'!$D$2:$D$2139))