"Jack Sons" wrote in message
...
Hi all,

A certain character or group of characters appears a number of times in
the
alpha numerical content (with different length) of some cells in A1:C100.
For example the group 3a appears twice in A5 = d763ah555#3abds3j and
twice in B21 = d73aah5&3ain39*h4 and once in B77 = W+3a3b$D34 but not in
column C. Cells with 0, blank or
"" are also possible.

I need to count
(1) the number of cells that contain the group 3a at least once

=COUNTIF(A1:C100,"*"&D1&"*")

(2) the number of cells that contain that group once

=COUNTIF(A1:C100,"*"&D1&"*")-COUNTIF(A1:C100,"*"&D1&"*"&D1&"*")

(3) the number of cells that contain that group twice

=COUNTIF(A1:C100,"*"&D1&"*"&D1&"*")-COUNTIF(A1:C100,"*"&D1&"*"&D1&"*"&D1&"*")

(4) the number of cells that contain that group three times

=COUNTIF(A1:C100,"*"&D1&"*"&D1&"*"&D1&"*")-COUNTIF(A1:C100,"*"&D1&"*"&D1&"*"&D1&"*"&D1&"*")

(5) the total number of occurrences of the group in the range A1 to C100
(that is 5 in the example above)

=SUMPRODUCT(LEN(A1:C29)-LEN(SUBSTITUTE(A1:C29,D1,"")))/LEN(D1)