Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT multiple critera from different worksheets
This is the first time I've used Sumproduct and I've got it working on some
sample date as below. Useing three critera, column a and b are text values with c and d being numeric values. =SUMPRODUCT((A2:A100=F1)*(B2:B100=G1)*(C2:C100=H1) *(D2:D100)) (this works fine for my example copied from a previous posting) I'm not trying to use this for real but with the lookup tables in another worksheet but the same data and the same inputs. The data is not formatted other than as the default general. I've seen other examples online of sumproduct linked to other worksheets and this is the same basic formula as above. However the output per row (for multiple input data) is always zero on every set of data. =SUMPRODUCT(('Oracle Code Lookup'!F2:F17=J4)*('Oracle Code Lookup'!G2:G17=I4)*('Oracle Code Lookup'!H2:H17=K4)*('Oracle Code Lookup'!I2:I17)) This looks ok to be other than the answer so I presume I've done something wrong but can't see it. Any help much appriciated. TIA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT multiple critera from different worksheets
Try one criteria to see if it OK, then increase another criteria and so on...
=SUMPRODUCT(('Oracle Code Lookup'!F2:F17=J4)*('Oracle Code Lookup'!I2:I17)) "nx3" wrote: This is the first time I've used Sumproduct and I've got it working on some sample date as below. Useing three critera, column a and b are text values with c and d being numeric values. =SUMPRODUCT((A2:A100=F1)*(B2:B100=G1)*(C2:C100=H1) *(D2:D100)) (this works fine for my example copied from a previous posting) I'm not trying to use this for real but with the lookup tables in another worksheet but the same data and the same inputs. The data is not formatted other than as the default general. I've seen other examples online of sumproduct linked to other worksheets and this is the same basic formula as above. However the output per row (for multiple input data) is always zero on every set of data. =SUMPRODUCT(('Oracle Code Lookup'!F2:F17=J4)*('Oracle Code Lookup'!G2:G17=I4)*('Oracle Code Lookup'!H2:H17=K4)*('Oracle Code Lookup'!I2:I17)) This looks ok to be other than the answer so I presume I've done something wrong but can't see it. Any help much appriciated. TIA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT multiple critera from different worksheets
I got it, both formulas were ok but one of the source looksup had spaces
tagged on the end and cause the problem. I've used TRIM to clean up the inputs and all working, thanks. "Teethless mama" wrote: Try one criteria to see if it OK, then increase another criteria and so on... =SUMPRODUCT(('Oracle Code Lookup'!F2:F17=J4)*('Oracle Code Lookup'!I2:I17)) "nx3" wrote: This is the first time I've used Sumproduct and I've got it working on some sample date as below. Useing three critera, column a and b are text values with c and d being numeric values. =SUMPRODUCT((A2:A100=F1)*(B2:B100=G1)*(C2:C100=H1) *(D2:D100)) (this works fine for my example copied from a previous posting) I'm not trying to use this for real but with the lookup tables in another worksheet but the same data and the same inputs. The data is not formatted other than as the default general. I've seen other examples online of sumproduct linked to other worksheets and this is the same basic formula as above. However the output per row (for multiple input data) is always zero on every set of data. =SUMPRODUCT(('Oracle Code Lookup'!F2:F17=J4)*('Oracle Code Lookup'!G2:G17=I4)*('Oracle Code Lookup'!H2:H17=K4)*('Oracle Code Lookup'!I2:I17)) This looks ok to be other than the answer so I presume I've done something wrong but can't see it. Any help much appriciated. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMif or SUMproduct across multiple worksheets? | Excel Worksheet Functions | |||
Line chart from multiple worksheets | Charts and Charting in Excel | |||
What is the quickest method to insert & name multiple worksheets . | Excel Worksheet Functions | |||
Adding and Naming Multiple Worksheets | Excel Worksheet Functions | |||
fax multiple worksheets | Excel Worksheet Functions |