Sumifs with posetive criteria array and negative criteria array

[Rsdg]

First of all sorry for my English
Hello , i have problem with sumifs formula, I have 3 condition and one of them is negative, i tried this formula but the answers is just effect one of my negative cell , i have the example table for my problem in the attachment , thanks

{=SUM(SUMIFS(L12:L20,H12:H20,B2,J12:J20,H2:J2,K12:K20,"<>"&H3:J3))}

 

[StephenCrump]

Welcome to the Forum!

Your formula is actually summing income for three possibilities, and adding these together:
- topic code = 219 and specified code <> 9,
- topic code = 221 and specified code <> 10, and
- topic code = 233 and specified code <>11.

I think what you want is:

=SUMPRODUCT(L12:L20,--(H12:H20=B2),--ISNUMBER(MATCH(J12:J20,H2:J2,)),--ISERROR(MATCH(K12:K20,H3:J3,)))

and you probably want to test city as well?

=SUMPRODUCT(L12:L20,--(H12:H20=B2),--(I12:I20=C2),--ISNUMBER(MATCH(J12:J20,H2:J2,)),--ISERROR(MATCH(K12:K20,H3:J3,)))

When you next post, please use XL2BB (rather than attaching a picture) which you can download here: XL2BB - Excel Range to BBCode

Then we copy your example directly into Excel, rather than having to re-type.

 

[Rsdg]

Welcome to the Forum!

Your formula is actually summing income for three possibilities, and adding these together:
- topic code = 219 and specified code <> 9,
- topic code = 221 and specified code <> 10, and
- topic code = 233 and specified code <>11.

I think what you want is:

=SUMPRODUCT(L12:L20,--(H12:H20=B2),--ISNUMBER(MATCH(J12:J20,H2:J2,)),--ISERROR(MATCH(K12:K20,H3:J3,)))

and you probably want to test city as well?

=SUMPRODUCT(L12:L20,--(H12:H20=B2),--(I12:I20=C2),--ISNUMBER(MATCH(J12:J20,H2:J2,)),--ISERROR(MATCH(K12:K20,H3:J3,)))

When you next post, please use XL2BB (rather than attaching a picture) which you can download here: XL2BB - Excel Range to BBCode

Then we copy your example directly into Excel, rather than having to re-type.

Thank you , your formula worked , thanks again

 

[StephenCrump]

Great. I'm glad we could help.