count number of appearences

[Jack Sons]

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
(2) the number of cells that contain that group once
(3) the number of cells that contain that group twice
(4) the number of cells that contain that group three times
(5) the total number of occurrences of the group in the range A1 to C100
(that is 5 in the example above)

The group 3a is in D1

I know that I can use
=(LEN(A1)-LEN(SUBSTITUTE(A1,D1,"")))/(LEN(D1))
to determine the number of occurrences of 3a in A1.

I think this formula could be used as an array formula to find the answers
to (1) to (5) but I don't know how to do that.

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Lori]

Select a range e.g. E1:E5 and then enter the array formula
(ctrl+Shift+enter to execute):

=FREQUENCY((LEN(A1:C100)-LEN(SUBSTITUTE(A1:C100,D1,"")))/(LEN(D1)),{0,1,2,3,4})

This gives the frequency of occurences occuring 0,1,2,3 and 4 or more
times. For the total number of occurences try:

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

 

[Bob Phillips]

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)

 

[Jack Sons]

Lori and Bob,

Thank you both.

Jack.

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