Count text then add results
 

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")+CO UNTIF(C47:G47,"xxx")+COUNTIF(C47:G47,"xxxx")+COUNT IF(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!

Count text then add results
 

If there is nothing but "x"s in the range, then
=SUM(LEN(C47:G47))
entered as an array formula (Control Shift Enter).

If there are other letters apart from the "x"s, then
=SUM(LEN(C47:G47))-SUM(LEN(SUBSTITUTE(C47:G47,"x","")))
again as an array formula.

Note that in your formula you didn't need SUM(), as you were adding the
COUNTIFs with the + operator, and it doesn't need SUM as well as +.
--
David Biddulph

"TonyH" wrote in message
...
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")+CO UNTIF(C47:G47,"xxx")+COUNTIF(C47:G47,"xxxx")+COUNT IF(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!

Count text then add results
 

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" wrote:

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")+CO UNTIF(C47:G47,"xxx")+COUNTIF(C47:G47,"xxxx")+COUNT IF(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!

Count text then add results
 

Thanks David ,

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