How do I count the number of cells in a range that contain the character
substring xxx?

I know =COUNTIF($G$8:$G$11,"kkkk") counts cells that are exactly kkkk, but i
want to count cells that congaing a substring.

I appreciate your help, -John

On Sun, 22 Feb 2009 03:07:01 -0800, John
<(E-Mail Removed)> wrote:

>How do I count the number of cells in a range that contain the character
>substring xxx?
>
>I know =COUNTIF($G$8:$G$11,"kkkk") counts cells that are exactly kkkk, but i
>want to count cells that congaing a substring.
>
>I appreciate your help, -John

Try this formula:

=SUMPRODUCT(--NOT(ISERROR(FIND("kkkk",G8:G11))))

If you don't care about lower and upper case, you can try this:

=SUMPRODUCT(--NOT(ISERROR(SEARCH("kkkk",G8:G11))))

Hope this helps / Lars-Åke

Hi,

You are almost there:

=COUNTIF($G$8:$G$11,"*xxx*")

* is the wildcard for anything so this says anything containing xxx.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"John" wrote:

> How do I count the number of cells in a range that contain the character
> substring xxx?
>
> I know =COUNTIF($G$8:$G$11,"kkkk") counts cells that are exactly kkkk, but i
> want to count cells that congaing a substring.
>
> I appreciate your help, -John

Put the substring in a cell:

A1 = kkkk

then:

=COUNTIF($G$8:$G$11,"*"&A1&"*")

--
Biff
Microsoft Excel MVP


"John" <(E-Mail Removed)> wrote in message
news:6EDA0FED-5E02-4AED-AFC0-(E-Mail Removed)...
> How do I count the number of cells in a range that contain the character
> substring xxx?
>
> I know =COUNTIF($G$8:$G$11,"kkkk") counts cells that are exactly kkkk, but
> i
> want to count cells that congaing a substring.
>
> I appreciate your help, -John