Multiple conditions with SUM or COUNT

[MichaelCreamer]

I am trying to using the following formula to calculate the sum of values in column C where 1) The ** appears anywhere in the value of column A and 2) the value of "Red" is in column B.

=SUMPRODUCT((A1:A6="*~*~*")*(B1:B6="Red")*(C1:C6))

The wildcard * does not seem to work here. (It does seem to work in COUNTIF and SUMIF but I need to check multiple conditions.)

Is there a way to use wildcard characters here to search for a string anywhere in the value?

I cannot use RIGHT(A1:A7,2), for instance, because the ** (or whatever string) may not always be at the end.

Thanks for any and all help!
Michael

 

[P Sitaram]

Try:

=SUMPRODUCT((ISNUMBER(SEARCH("~*~*",A1:A6)))*(B1:B6="Red")*(C1:C6))

 

[Aladin Akyurek]

Using cell references instead of hard-coding conditions...

=SUMPRODUCT(--ISNUMBER(SEARCH(SUBSTITUTE(E1,"*","~*"),$A$1:$A$7)),--($B$1:$B$7=E2),$C$1:$C$7)

where E1 houses ** and E2 Red.

 

[MichaelCreamer]

Thanks guys! These suggestions work like a charm!

Thanks again!