Sumproduct or DSum help

[MartinL]

Hi

I need to sum the values where
A2:A517="x"
AND
H2:H517="y" Or H2:H517="z"
the data to be summed is in IH2:IH517

I can write a sumproduct to do OR
I can write a sumproduct to do AND
but not both

research suggest using DSUM but I do not understand how to apply it to this query.

can anyone help

 

[VoG]

Try

=SUMPRODUCT(--(A2:A517="x"),--ISNUMBER(MATCH(H2:H517,{"y","z"},0)),IH2:IH517)

 

[MartinL]

Hi VoG

Love the idea, but it didnt seem to work I got 0 returned

M

 

[VoG]

Hmm. It worked for me.

 

[MartinL]

Hmm. It worked for me.

OK I'm the dummy, of course it works

(I miss-typed a column letter, Old fat fingers)

Martin

 

[MartinL]

Can I ammend this to be
A2:A517="x"
AND
H2:H517>= 123 AND H2:H517<=456

I've tried these two but get 0 returned

=SUMPRODUCT(1*(B2:B104281=N3),1*(J2:J104281>=P1),1*(J2:J104281<=W1)*E2:E104281)

=SUMPRODUCT(--(B2:B104281=N3),--(J2:J104281>=P1),--(J2:J104281<=W1)*E2:E104281)

 

[MartinL]

Ignore me I'm a tw*t If I had put in the correct column (again) the formula would have worked!!!