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.

Oppss.. Sorry,

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

"Sampoerna" wrote in message
...
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.

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


On 18 Mar, 13:53, "Sampoerna" wrote:
Oppss.. Sorry,

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

"Sampoerna" wrote in message

...



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.- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

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



"Sampoerna" wrote:

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.

Hi,

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

=COUNT(SEARCH(" was ",D5:D7,1))


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Sampoerna" wrote in message
...
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.

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" wrote in message
...
Oppss.. Sorry,

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

"Sampoerna" wrote in message
...
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.

It works, thanks Jarek that was a quick respond.

"Jarek Kujawa" wrote in message
...
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


On 18 Mar, 13:53, "Sampoerna" wrote:
Oppss.. Sorry,

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

"Sampoerna" wrote in message

...



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.- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

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" wrote in message
...
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



"Sampoerna" wrote:

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.

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" wrote:

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" wrote in message
...
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



"Sampoerna" wrote:

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.

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.


"Ron Coderre" wrote in message
...
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" wrote in message
...
Oppss.. Sorry,

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

"Sampoerna" wrote in message
...
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.

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 :)

"Ashish Mathur" wrote in message
...
Hi,

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

=COUNT(SEARCH(" was ",D5:D7,1))


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Sampoerna" wrote in message
...
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.

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 :)


"francis" wrote in message
...
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" wrote:

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" wrote in message
...
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



"Sampoerna" wrote:

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.