Counting cells that contain a substring

[John]

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

 

[Lars-Åke Aspelin]

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

 

[Shane Devenshire]

Hi,

You are almost there:

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

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

 

[T. Valko]

Put the substring in a cell:

A1 = kkkk

then:

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