Sum-product multiple conditions

[AV114]

I run into several postings on the internet with code like this

=SUMPRODUCT((A1:A10="Marketing")*(B1:B10={"North","South"})*(D1:D10={"JAN","FEB"})*(E1:E10={"JOHN","SMITH"})*(C1:C10))

Conditions for search are neatly put into {}. I have such conditions to search for, so I'm looking for a way to make the formula easier to read. If I try it, i get N/A.

Is there a trick I'm missing?

Thank you in advance

 

[bosco_yip]

Try to use this formula instead :

=SUMPRODUCT((A1:A10="Marketing")*ISNUMBER(MATCH(B1:B10,{"North","South"},0))*ISNUMBER(MATCH(D1:D10,{"JAN","FEB"},0))*C1:C10)

Regards
Bosco

 

[AV114]

Try to use this formula instead :

=SUMPRODUCT((A1:A10="Marketing")*ISNUMBER(MATCH(B1:B10,{"North","South"},0))*ISNUMBER(MATCH(D1:D10,{"JAN","FEB"},0))*C1:C10)

Regards
Bosco

Thanks !!!