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