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



--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
80 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!

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)))



Jack Sons wrote:

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



--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
80 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!

"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)

Lori and Bob,

Thank you both.

Jack.

"Bob Phillips" schreef in bericht
...


"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)




--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
80 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!