sum product with criteria from 2 wkshts

[brownmre]

I have been using an array that all of the sudden I can't get to work at
all. I am working with 2 worksheets (with data queries). Where the product
code and Plant matches in both worksheets, I want to add the sum of cases
from wksht B to wksht A. Can someone tell me what I am doing wrong? My
formula looks like this

=SUMPRODUCT(('[Production Data-Inv Report.xls]PRODUCTION
DATA'!$C$2:$C$1000=B6)*('[Production Data-Inv Report.xls]PRODUCTION
DATA'!$G$2:$G$1000=E6)*('[Production Data-Inv Report.xls]PRODUCTION
DATA'!$D$2:$D$1000))

Where '[Production Data-Inv Report.xls]PRODUCTION DATA' equals wksht B.
Right now I am getting a 0 in every cell.

if

WKSHT "A"
B E L
Prod Code Plant On Order
SAMPLE SKO
08608-6508 TNB
09400-7864 ADV
09449-6629 ADV

WKSHT "B"
B D G
PROD_CODE CASES PLANT
08608-6508 1632 TNB
08608-6508 2160 TNB
09400-7864 720 ADV
09449-6629 720 ADV

 

[Max]

Right now I am getting a 0 in every cell

The above usually means that apparent good matches are being thrown off due
to extraneous white spaces somewhere in one or both data sets
(source/target). Since sumproduct allows TRIM, you can try wrapping TRIM
around both source/target data to increase the robustness of the matching,
like this:

=SUMPRODUCT((TRIM('[Production Data-Inv Report.xls]PRODUCTION
DATA'!$C$2:$C$1000)=TRIM(B6))*(TRIM('[Production Data-Inv
Report.xls]PRODUCTION DATA'!$G$2:$G$1000)=TRIM(E6))*('[Production Data-Inv
Report.xls]PRODUCTION DATA'!$D$2:$D$1000))

Above lightly tested ok here. Remember to high-five it by clicking the YES
button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik

 

[brownmre]

I have added the trim and I get a message that my formula has an error and
asking whether I want to accept the corrections. Excel adds to end
parentheses to my formula and I get the result of #VALUE! in the cell. The
full file path is also now showing up in the formula. This is what my
formula looks like now:

=SUMPRODUCT(('C:\Documents and Settings\kimberly\My Documents\My Data
Sources\[Production Data-Inv Report.xls]PRODUCTION
DATA'!$B$2:$B$2000=B7)*('C:\Documents and Settings\kimberly\My Documents\My
Data Sources\[Production Data-Inv Report.xls]PRODUCTION
DATA'!$G$2:$G$2000=E7)*('C:\Documents and Settings\kimberly\My Documents\My
Data Sources\[Production Data-Inv Report.xls]PRODUCTION DATA'!$D$2:$D$2000))

Right now I am getting a 0 in every cell

The above usually means that apparent good matches are being thrown off due
to extraneous white spaces somewhere in one or both data sets
(source/target). Since sumproduct allows TRIM, you can try wrapping TRIM
around both source/target data to increase the robustness of the matching,
like this:

=SUMPRODUCT((TRIM('[Production Data-Inv Report.xls]PRODUCTION
DATA'!$C$2:$C$1000)=TRIM(B6))*(TRIM('[Production Data-Inv
Report.xls]PRODUCTION DATA'!$G$2:$G$1000)=TRIM(E6))*('[Production Data-Inv
Report.xls]PRODUCTION DATA'!$D$2:$D$1000))

Above lightly tested ok here. Remember to high-five it by clicking the YES
button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

I have been using an array that all of the sudden I can't get to work at
all. I am working with 2 worksheets (with data queries). Where the product
code and Plant matches in both worksheets, I want to add the sum of cases
from wksht B to wksht A. Can someone tell me what I am doing wrong? My
formula looks like this

=SUMPRODUCT(('[Production Data-Inv Report.xls]PRODUCTION
DATA'!$C$2:$C$1000=B6)*('[Production Data-Inv Report.xls]PRODUCTION
DATA'!$G$2:$G$1000=E6)*('[Production Data-Inv Report.xls]PRODUCTION
DATA'!$D$2:$D$1000))

Where '[Production Data-Inv Report.xls]PRODUCTION DATA' equals wksht B.
Right now I am getting a 0 in every cell.

if

WKSHT "A"
B E L
Prod Code Plant On Order
SAMPLE SKO
08608-6508 TNB
09400-7864 ADV
09449-6629 ADV

WKSHT "B"
B D G
PROD_CODE CASES PLANT
08608-6508 1632 TNB
08608-6508 2160 TNB
09400-7864 720 ADV
09449-6629 720 ADV

 

[Max]

I have added the trim ..
But I don't see the suggested TRIM applied anywhere in your expression?

The full file path is also now showing up in the formula ..

It's always ugly and eyeball searing if the source file's closed. Open up
that source file (and keep it open at the same time), and your expression (in
your other book) instantly becomes amazingly simpler and easier on the eyes.

With the source file open,
Copy the corrected expression below n paste directly into the formula cell
in the other book:
=SUMPRODUCT((TRIM('[Production Data-Inv Report.xls]PRODUCTION
DATA'!$B$2:$B$2000)=TRIM(B7))*(TRIM('[Production Data-Inv
Report.xls]PRODUCTION DATA'!$G$2:$G$2000)=TRIM(E7))*'[Production Data-Inv
Report.xls]PRODUCTION DATA'!$D$2:$D$2000)

I noticed that you've changed the point from col C to col B in your
expression, and extended the range from row 1000 to row 2000 (compared to
what you posted originally)

The above should now work fine. If it still returns #VALUE, that means col D
(the sum range) contains text somewhere. Use autofilter to check that col,
clean up the errants, and it'll return correctly.

Do a high-five here, click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

I have added the trim and I get a message that my formula has an error and
asking whether I want to accept the corrections. Excel adds to end
parentheses to my formula and I get the result of #VALUE! in the cell. This is what my
formula looks like now:

=SUMPRODUCT(('C:\Documents and Settings\kimberly\My Documents\My Data
Sources\[Production Data-Inv Report.xls]PRODUCTION
DATA'!$B$2:$B$2000=B7)*('C:\Documents and Settings\kimberly\My Documents\My
Data Sources\[Production Data-Inv Report.xls]PRODUCTION
DATA'!$G$2:$G$2000=E7)*('C:\Documents and Settings\kimberly\My Documents\My
Data Sources\[Production Data-Inv Report.xls]PRODUCTION DATA'!$D$2:$D$2000))