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

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

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