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
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 the

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