Sumproduct which counts Text in cell

  • Thread starter Thread starter jxbeeman
  • Start date Start date
J

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
 
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
 
If the Case of Q is important then (upper or lower)

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