Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum product with criteria from 2 wkshts
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum product with criteria from 2 wkshts
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum product with criteria from 2 wkshts
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum product with criteria from 2 wkshts
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 --- "brownmre" wrote: 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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for product based on criteria? | Excel Worksheet Functions | |||
product between two dates and with criteria | Excel Worksheet Functions | |||
SUMIF/PRODUCT with multiple Criteria not working | Excel Discussion (Misc queries) | |||
SUMPRODUCT, two criteria (date and product type) | Excel Worksheet Functions | |||
Product of 2 arrays based on criteria | Excel Discussion (Misc queries) |