SUMPRODUCT multiple critera from different worksheets


N

nx3

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
 
Ad

Advertisements

T

Teethless mama

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))
 
Ad

Advertisements

N

nx3

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top