Count Cell that contain a word.

[Sampoerna]

Hi,
I want to count how many cells are they that have a specific word. e.g.
A1= He was watching
A2= She was washing
A3= Washing is fun

which is equal to 2

Thanks in advance for any help.

 

[Sampoerna]

Oppss.. Sorry,

There is missing word. And the target Word should be " was " corresponding
to previously given example

 

[Jarek Kujawa]

try:

=SUM(IF(ISNUMBER(FIND(" was ",$A$1:$A$3,1)),1,))

CTRL+SHIFT+ENTER it as it is an array-formula

pls click YES if it was helpful

 

[francis]

Assume that you want to count how many times washing appear in the range
try
=COUNTIF(A1:A3,"*"&"washing"&"*")

This is not case sensitive so its counts both A2 and A3


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

 

[Ashish Mathur]

Hi,

You can also try this array formula (Ctrl+Shift+Enter)

=COUNT(SEARCH(" was ",D57,1))


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ron Coderre]

This regular formula counts cells that:
.. Contain only "was"
.. Start with the word "was"
.. End with the word "was"
.. Contain "was"
Excluding "was" as part of a word...eg "wasn't"

=SUM(COUNTIF(A1:A3,{"was","was *","* was","* was *"}))

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)

 

[Sampoerna]

It works, thanks Jarek that was a quick respond.

try:

=SUM(IF(ISNUMBER(FIND(" was ",$A$1:$A$3,1)),1,))

CTRL+SHIFT+ENTER it as it is an array-formula

pls click YES if it was helpful

 

[Sampoerna]

Thanks francis,

Amazing! I did make a countif formula before. But not works until I saw this
one. I wish I knew it earlier to use the wildcard "*". I'm so glad to see
this one works. Thanks again.

 

[francis]

Hi Sampoerna

I didn't realized that you have posted a 2nd post on the criteria,
this will count the numbers of "was" occurrences in the range

=COUNTIF(A1:A3,"*"&" "&"was"&" "&"*")
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked
An ordinary user helping another ordinary user and learn together

Thank You

cheers, francis

 

[Sampoerna]

Thanks Ron,

Glad to have you with us. I can see how usefull when we could expand the
word search possibilities.
Yes! very helpfull and formula works as needed.

 

[Sampoerna]

Thanks Ashish Tathur,

My appreciations on your help. I'm so glad to know that your given formula
works nicely. Believe me, I also tried many ways creating the count and
search formula but failed.

Cheers

 

[Sampoerna]

Not to worry... I still can follow your formula which works nicely.

All the best to all of you who could help us. You all are amazing!
Thanks a million.

Cheers