Sumproduct with column that "contains" instead of "equals"

[Portuga]

Hi,

Tried to no avail, find the answer for this question:

I wish to count values with the following criteria:

Values that in columnA are equal to "pass"
Values that in columnB contain the string "blue"

Because I cant use the formula:
=SUMPRODUCT((A1:A11="Pass")*(B1:B11="*Blue*"))

I tried the following sumproduct formula, but it doesn´t work.
=SUMPRODUCT((A1:A11="Pass")*(COUNTIF(B1:B11,"*Blue*")))

After some unsuccessfull sum boolean aproaches here I am.

I could use helper columns and an if statement by row:
=If(and(A1="pass",countif(B1,"*blue*")),1,0)

And sum the column but I would rather have a 1 formula solution.



Thanks

[darkyam]

Please Login or Register  to view this content.

[Portuga]

excelent! thanks