Counting cells with partial text

M

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!
 
J

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.
 
D

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.
 
D

David Biddulph

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

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 *")
 
M

Makaron

Yes - thank you for the observation!

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

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!
 
M

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!
 
D

David Biddulph

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

See countless questions and answers in this group archives.
 
J

JE McGimpsey

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

Yup - I failed to read the desired value and was using OR as inclusive.
 
Top