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!

Answer: Counting cells with partial text
 

To count cells that contain at least a certain word or entry in Microsoft Excel, follow these steps:

1. Select the cell where you want to display the result of the count.
2. Type the following formula: =COUNTIF(range,"*word*")
   Replace "range" with the range of cells you want to count, and replace "word" with the word or entry you want to count.
   For example, to count cells that contain "he" or "she" in the range A1:A3, you would type: =COUNTIF(A1:A3,"*he*")+COUNTIF(A1:A3,"*she*")
   To count cells that contain "it" in the same range, you would type: =COUNTIF(A1:A3,"*it*")
3. Press Enter to display the result.

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.

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!

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!

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!

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!

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!

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!

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!

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!

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.