Count text then add results

[TonyH]

I wish to add a row of cells that contain “x†or “xx†or “xxx†etc this I can
do with:-

=SUM(COUNTIF(C47:G47,"x")+COUNTIF(C47:G47,"xx")+COUNTIF(C47:G47,"xxx")+COUNTIF(C47:G47,"xxxx")+COUNTIF(C47:G47,"xxxxx"))

The above gives me a count of the number of “cells†containing my criteria:-

However I wish to count the occurrences, contents of the cells: of how many
“x†in the range?


e.g.
c d e e f
x xx x xxx = 7

I am new to this nay help would be gratefully received!

 

[Mike H]

Tony,

Try this. Adjust the range to suit. Note this does both Upper and Lower case
X. If you want only one the delete the one you don't want from the formula

=SUMPRODUCT(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,{"x","X"},"")))

Note this is an array and must ben entered by pressing CTRL+Shift+Enter NOT
just enter. If you do it correctly then Excel will put curly brackets around
the formula {}. You can't type these yourself.

Mike

 

[TonyH]

Thanks David ,

works OK the second time I tried, forget the Control Shift Enter first time!!