Sumproduct help
 

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))

Sumproduct help
 

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))

Sumproduct help
 

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))

Sumproduct help
 

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))

Sumproduct help
 

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))

Sumproduct help
 

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))

Sumproduct help
 

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))