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