Re: Countif with two columns-one exact and one containing

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 04/15/04 

Date: Fri, 16 Apr 2004 00:39:51 +0200

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

--
Regards
Frank Kabel
Frankfurt, Germany
bxb7668 wrote:
> 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
Quantcast