Count occurrences of a substring in an array
 

Does anyone have an example of counting the number cells in a column (e.g.
D5:D30) which contain a substring using the contents of another cell as the
substring? The position of the substring in the cell contents is not
important. Also the data does not make it possible for the substring to occur
more than once in any cell.

For example:

If A10 contained "car" as the substring and D5:D30 contained various text
strings, two of them "carrot", and the remainder containing "X", the function
would return the numeric value 2.

Thanks!

Michael

Count occurrences of a substring in an array
 

Try,

=COUNTIF(D5:D30,"*Car*")

Mike

"Michael J. O''Connell" wrote:

Does anyone have an example of counting the number cells in a column (e.g.
D5:D30) which contain a substring using the contents of another cell as the
substring? The position of the substring in the cell contents is not
important. Also the data does not make it possible for the substring to occur
more than once in any cell.

For example:

If A10 contained "car" as the substring and D5:D30 contained various text
strings, two of them "carrot", and the remainder containing "X", the function
would return the numeric value 2.

Thanks!

Michael

Count occurrences of a substring in an array
 

Try this:

=Countif(D5:D30,"*"&A10&"*")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Michael J. O''Connell" wrote
in message ...
Does anyone have an example of counting the number cells in a column (e.g.
D5:D30) which contain a substring using the contents of another cell as
the
substring? The position of the substring in the cell contents is not
important. Also the data does not make it possible for the substring to
occur
more than once in any cell.

For example:

If A10 contained "car" as the substring and D5:D30 contained various text
strings, two of them "carrot", and the remainder containing "X", the
function
would return the numeric value 2.

Thanks!

Michael

Count occurrences of a substring in an array
 

Many thanks. Works like a charm!

Michael

"RagDyer" wrote:

Try this:

=Countif(D5:D30,"*"&A10&"*")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Michael J. O''Connell" wrote
in message ...
Does anyone have an example of counting the number cells in a column (e.g.
D5:D30) which contain a substring using the contents of another cell as
the
substring? The position of the substring in the cell contents is not
important. Also the data does not make it possible for the substring to
occur
more than once in any cell.

For example:

If A10 contained "car" as the substring and D5:D30 contained various text
strings, two of them "carrot", and the remainder containing "X", the
function
would return the numeric value 2.

Thanks!

Michael

Count occurrences of a substring in an array
 

You're welcome ... Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Michael J. O''Connell" wrote
in message ...
Many thanks. Works like a charm!

Michael

"RagDyer" wrote:

Try this:

=Countif(D5:D30,"*"&A10&"*")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Michael J. O''Connell" wrote
in message ...
Does anyone have an example of counting the number cells in a column
(e.g.
D5:D30) which contain a substring using the contents of another cell as
the
substring? The position of the substring in the cell contents is not
important. Also the data does not make it possible for the substring to
occur
more than once in any cell.

For example:

If A10 contained "car" as the substring and D5:D30 contained various
text
strings, two of them "carrot", and the remainder containing "X", the
function
would return the numeric value 2.

Thanks!

Michael