Sumproduct using one range criteria and one fixe cell

[cstlaurent]

HI am trying to use the sumproduct using two criteria, wich it's ok up to now. But when one of the criteria as took compare the data to a range it is not working. EX: =+Sumproduct((A2:A7=(A20:a24)*(B2:B7=B20)*(C2:C7)).
I am able to use an array formula.. sum(if(or(A2:A6=A20;A2:A6=A21; and so and so);if(B2:B6=B20;C2:C6))). But was hoping there will be a simpler way using a range instead of indicating cell by cell the comparaison with the sumproduct funciton

thanks in advance

20001 REG 7.5
20001 ADD OT
20001 REG 7.5
20002 REG 7.5
20003 REG 7.5


range Where
20001 REG
20003
and so
and so

 

[Brian from Maui]

Try,

=SUMPRODUCT(--(ISNUMBER(MATCH(A7:A20,A20:A24,0))),--(B2:B7=B20),C2:C7)

 

[cstlaurent]

Unfortunately the Match Function doesn't seem to accept a range for the value to look into the search range.

It doesn't work.

 

[just_jon]

Maybe a slight tweak --

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A7,A20:A24,0))),--(B2:B7=B20),C2:C7)

 

[Brian from Maui]

Maybe a slight tweak --

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A7,A20:A24,0))),--(B2:B7=B20),C2:C7)

Thanks Jon......

 

[cstlaurent]

Unfortunately the MAtch Lookup Value, will not accept an array, it's giving me an #value.

 

[just_jon]

Can you post the formula as it now stands?

 

[KenWright]

From the look of it your value column that is being summed contains text, ie the OT bit. SUMPRODUCT doesn't like that. Is that how your data is, a mixture of text and numbers?

Ken.........

 

[cstlaurent]

He the formula I have tried .

=+SUMPRODUCT((ISNUMBER(MATCH(A11:A15;A2:A7;0)))*(B2:B7=B10)*C2:C7)

When I remove the a15) it does work. The Match Value doesn't work with an array selection).

so far the only formla that is working is
=+SUMPRODUCT(OR((A2:A7=A11);A2:A7=A12)*(B2:B7=B11)*(C2:C7))

But I have place where I have 2 criteria and other 6. So I am trying to replace the (or((a2:A7=a11);a2:a7 = A12) by something that will compare the selected array to an criteria array.

 

[just_jon]

He the formula I have tried .

=+SUMPRODUCT((ISNUMBER(MATCH(A11:A15;A2:A7;0)))*(B2:B7=B10)*C2:C7)

When I remove the a15) it does work. The Match Value doesn't work with an array selection).

so far the only formla that is working is
=+SUMPRODUCT(OR((A2:A7=A11);A2:A7=A12)*(B2:B7=B11)*(C2:C7))

But I have place where I have 2 criteria and other 6. So I am trying to replace the (or((a2:A7=a11);a2:a7 = A12) by something that will compare the selected array to an criteria array.

I think you've got 2 of the arguments reversed...

=+SUMPRODUCT((ISNUMBER(MATCH(A2:A7;A11:A15;0)))*(B2:B7=B10)*C2:C7)