Countif with two columns-one exact and one containing

[bxb7668]

I need to count the number of rows where column A contains the exact
value "Product" and column B contains the value "widget". By
"contains" I mean that it may be "Red widget" or "Widget stopper" or
"Blue widget thing" or may even be just "Widget". Searching this group
I came up with the sumproduct function but it does not seem to handle
"contains". I'm trying:
=SUMPRODUCT(--(A2:A156="Product"),--(B2:B156="*widget*"))
or
=SUMPRODUCT((A2:A156="Product")*(B2:B156="*widget*"))

Any suggestions?
Brian

 

[Frank Kabel]

Hi Brian
one way:
=SUMPRODUCT(--(A2:A156="Product"),--ISNUMBER(FIND("widget",B2:B156)))

 

[bxb7668]

Cool! That's real close. "Widget" might or might not be capitalized.
This only finds the exact case. Is there a way to make it not care if
it is capitalized or not?

 

[Harlan Grove]

Cool! That's real close. "Widget" might or might not be capitalized.
This only finds the exact case. Is there a way to make it not care if
it is capitalized or not?

...

Use SEARCH instead of FIND.

 

[bxb7668]

Thank you very much Frank and Harlan. Have a good (almost) weekend.