Sumproduct which counts Text in cell

[jxbeeman]

Hello,
I'm trying to use the sumproduct formulat to count all the cells that
contain say a Q in them somewhere. the "Q" may not be in the same place all
the time and the length of the string may be different between cells.
For Example:
HHQH
HHHHQHHH
********
**Q****
********
**
*****
So for this example i would want the answer to be 3.

Thank you in advance,
Josh

 

[Mike H]

Hi,

Try this

=SUMPRODUCT(ISNUMBER(SEARCH("Q",A1:A10))*1)

Mike

 

[Pete_UK]

Try this:

=COUNTIF(A:A,"*Q*")

assuming you are looking in column A.

You can't use wildcards in the same way with SUMPRODUCT - you would
need to have a term like:

--ISNUMBER(SEARCH("Q",range))

within the formula.

Hope this helps.

Pete

 

[T. Valko]

Try this:

=COUNTIF(A1:A10,"*Q*")

 

[Shane Devenshire]

If the Case of Q is important then (upper or lower)

=SUMPRODUCT(--ISNUMBER(FIND("q",A11:A21)))
or
=SUMPRODUCT(--ISNUMBER(FIND("Q",A11:A21)))