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

SUMPRODUCT doesn't support the use of wildcards.

Try it like this:

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

--
Biff
Microsoft Excel MVP


"excel al" <excel wrote in message
...
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

Al

SUMPRODUCT does not support wildcards. I can't answer your question but the
following link may help you.

http://www.xldynamic.com/source/xld....2.html#partial
--
Russell Dawson
Excel Student


"excel al" wrote:

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

Try

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

--
Jacob


"excel al" wrote:

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