Can SUMPRODUCT include an *?

[excel al]

I am using SUMPRODUCT formulas a lot in my spreadsheet (having recently
graduated from COUNTIF thanks to reading through these forums!).

However often my cells have multiple words, and I am just looking for one
(poor database design, I know). Usually I would use the * before and after
my word to indicate that any other characters could come before or after, but
this doesn't seem to work with SUMPRODUCT?

Is there a way to make this work? Simplified example below:

A B
1 Pant Blue, red, yellow
2 Pant red
3 Top blue, yellow
4 Pant red, blue
5 Top yellow only

=SUMPRODUCT((A1:A5="Pant")*(Models!S3:S35="*blue*"))
returns a 0 result.

Many thanks,
Al

 

[T. Valko]

SUMPRODUCT doesn't support the use of wildcards.

Try it like this:

=SUMPRODUCT(--(A1:A5="Pant"),--(ISNUMBER(SEARCH("blue",B1:B5))))

 

[Jacob Skaria]

Try

=SUMPRODUCT((A1:A5="Pant")*(ISNUMBER(SEARCH("blue",B1:B5))))