Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells with partial text
I need to count cells that contain at least a certain word or entry, but not
neccesserily just that word. For example: 1) he she it 2) he it 3) she it I would like to get 2 if I count "he" or "she", and 3 if I count "it" Thank you very much! |
#2
|
|||
|
|||
Answer: Counting cells with partial text
To count cells that contain at least a certain word or entry in Microsoft Excel, follow these steps:
The COUNTIF function counts the number of cells in the specified range that meet the specified criteria. The asterisks (*) are used as wildcards to match any number of characters before or after the word or entry you want to count. By using the plus sign (+) between two COUNTIF functions, you can count cells that contain either of the two words or entries.
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells with partial text
One way:
=COUNTIF(A1:A10,"*he*") That of course, will also count "wheelbarrow". Using =COUNTIF(A1:A10,"*he *") is a litter more discriminating, if he or she will always be followed by a space, as in your examples. In article , Makaron wrote: I need to count cells that contain at least a certain word or entry, but not neccesserily just that word. For example: 1) he she it 2) he it 3) she it I would like to get 2 if I count "he" or "she", and 3 if I count "it" Thank you very much! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells with partial text
=COUNTIF(A$1:A$3,"*she*") will return 2
=COUNTIF(A$1:A$3,"*it*") will return 3 but =COUNTIF(A$1:A$3,"*he*") will return 3 as it includes he as part of she. -- David Biddulph "Makaron" wrote in message ... I need to count cells that contain at least a certain word or entry, but not neccesserily just that word. For example: 1) he she it 2) he it 3) she it I would like to get 2 if I count "he" or "she", and 3 if I count "it" Thank you very much! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells with partial text
But of course =COUNTIF(A1:A10,"*he *") still counts "she it", so gives 3,
rather than the OP's aspiration of 2. -- David Biddulph "JE McGimpsey" wrote in message ... One way: =COUNTIF(A1:A10,"*he*") That of course, will also count "wheelbarrow". Using =COUNTIF(A1:A10,"*he *") is a litter more discriminating, if he or she will always be followed by a space, as in your examples. In article , Makaron wrote: I need to count cells that contain at least a certain word or entry, but not neccesserily just that word. For example: 1) he she it 2) he it 3) she it I would like to get 2 if I count "he" or "she", and 3 if I count "it" Thank you very much! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells with partial text
do we know that if "he" is on the line that it will always be the first? then
=COUNTIF(A$1:A$3,"he *") "David Biddulph" wrote: =COUNTIF(A$1:A$3,"*she*") will return 2 =COUNTIF(A$1:A$3,"*it*") will return 3 but =COUNTIF(A$1:A$3,"*he*") will return 3 as it includes he as part of she. -- David Biddulph "Makaron" wrote in message ... I need to count cells that contain at least a certain word or entry, but not neccesserily just that word. For example: 1) he she it 2) he it 3) she it I would like to get 2 if I count "he" or "she", and 3 if I count "it" Thank you very much! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells with partial text
Yes - thank you for the observation!
"David Biddulph" wrote: But of course =COUNTIF(A1:A10,"*he *") still counts "she it", so gives 3, rather than the OP's aspiration of 2. -- David Biddulph "JE McGimpsey" wrote in message ... One way: =COUNTIF(A1:A10,"*he*") That of course, will also count "wheelbarrow". Using =COUNTIF(A1:A10,"*he *") is a litter more discriminating, if he or she will always be followed by a space, as in your examples. In article , Makaron wrote: I need to count cells that contain at least a certain word or entry, but not neccesserily just that word. For example: 1) he she it 2) he it 3) she it I would like to get 2 if I count "he" or "she", and 3 if I count "it" Thank you very much! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells with partial text
Thank you - that is what I was looking for - those *'s - the example was a
random one, and just not well thought-out... This helps! "JE McGimpsey" wrote: One way: =COUNTIF(A1:A10,"*he*") That of course, will also count "wheelbarrow". Using =COUNTIF(A1:A10,"*he *") is a litter more discriminating, if he or she will always be followed by a space, as in your examples. In article , Makaron wrote: I need to count cells that contain at least a certain word or entry, but not neccesserily just that word. For example: 1) he she it 2) he it 3) she it I would like to get 2 if I count "he" or "she", and 3 if I count "it" Thank you very much! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells with partial text
But is it possible to use same *'s with the AND() function? it doesnt seem to
work equally... (like i want to get true if one cell contains "*he*" and another something else) Thanks! "David Biddulph" wrote: =COUNTIF(A$1:A$3,"*she*") will return 2 =COUNTIF(A$1:A$3,"*it*") will return 3 but =COUNTIF(A$1:A$3,"*he*") will return 3 as it includes he as part of she. -- David Biddulph "Makaron" wrote in message ... I need to count cells that contain at least a certain word or entry, but not neccesserily just that word. For example: 1) he she it 2) he it 3) she it I would like to get 2 if I count "he" or "she", and 3 if I count "it" Thank you very much! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells with partial text
In that case you probably need SUMPRODUCT, rather than COUNTIF.
See countless questions and answers in this group archives. -- David Biddulph "Makaron" wrote in message ... But is it possible to use same *'s with the AND() function? it doesnt seem to work equally... (like i want to get true if one cell contains "*he*" and another something else) Thanks! "David Biddulph" wrote: =COUNTIF(A$1:A$3,"*she*") will return 2 =COUNTIF(A$1:A$3,"*it*") will return 3 but =COUNTIF(A$1:A$3,"*he*") will return 3 as it includes he as part of she. -- David Biddulph "Makaron" wrote in message ... I need to count cells that contain at least a certain word or entry, but not neccesserily just that word. For example: 1) he she it 2) he it 3) she it I would like to get 2 if I count "he" or "she", and 3 if I count "it" Thank you very much! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells with partial text
In article ,
"David Biddulph" <groups [at] biddulph.org.uk wrote: But of course =COUNTIF(A1:A10,"*he *") still counts "she it", so gives 3, rather than the OP's aspiration of 2. Yup - I failed to read the desired value and was using OR as inclusive. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding numbers based on partial text in adjacent cells | Excel Discussion (Misc queries) | |||
Counting cells that don't contain certain text | Excel Worksheet Functions | |||
Counting cells with text | Excel Worksheet Functions | |||
Counting Occurrence of Text within Text in Cells in Range. | Excel Worksheet Functions | |||
counting text cells | Excel Worksheet Functions |