Counting cells with partial text

[Makaron]

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!

 

[JE McGimpsey]

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.

 

[David Biddulph]

=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]

But of course =COUNTIF(A1:A10,"*he *") still counts "she it", so gives 3,
rather than the OP's aspiration of 2.

 

[Don]

do we know that if "he" is on the line that it will always be the first? then
=COUNTIF(A$1:A$3,"he *")

 

[Makaron]

Yes - thank you for the observation!

But of course =COUNTIF(A1:A10,"*he *") still counts "she it", so gives 3,
rather than the OP's aspiration of 2.

 

[Makaron]

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!

 

[Makaron]

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]

In that case you probably need SUMPRODUCT, rather than COUNTIF.

See countless questions and answers in this group archives.

 

[JE McGimpsey]

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.