Countif with two columns-one exact and one containing

  • Thread starter Thread starter bxb7668
  • Start date Start date
B

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
 
Hi Brian
one way:
=SUMPRODUCT(--(A2:A156="Product"),--ISNUMBER(FIND("widget",B2:B156)))
 
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?
 
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?
Click to expand...
...

Use SEARCH instead of FIND.
 
Back
Top