| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I return multiple occurrences of a match in an array? | Excel Worksheet Functions | |||
| How do I count repeat Occurrences | Excel Discussion (Misc queries) | |||
| Count names and occurrences | Excel Worksheet Functions | |||
| trying to COUNT occurrences when certain criteria is met | Excel Discussion (Misc queries) | |||
| Count unique occurrences of name | Excel Discussion (Misc queries) | |||
Linear Mode
