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!

 

[David Biddulph]

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 +.

 

[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!!