Sumproduct help

  • Thread starter Thread starter brownmre
  • Start date Start date
B

brownmre

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
 
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)
 
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 said:
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 said:
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))
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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))
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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)

:

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))
Click to expand...
Click to expand...
Click to expand...
 
Thanks, that worked. My 3rd criteria was causing the problem.



Jacob Skaria said:
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 said:
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 said:
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)

:

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))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top