Formula needed: count if cell CONTAINS certain text

[Wim]

What formula do I need to count the cells that CONTAIN a certain text
(<> equal!)
e.g.
A1 = John
A2 = Pieter
A3 = William

count cells that CONTAIN "i": 2 (A2 and A3)

What (set of) formula should I use? Excel Help doesn't give me the
answer.

Wim

 

[Ron Rosenfeld]

What formula do I need to count the cells that CONTAIN a certain text
(<> equal!)
e.g.
A1 = John
A2 = Pieter
A3 = William

count cells that CONTAIN "i": 2 (A2 and A3)

What (set of) formula should I use? Excel Help doesn't give me the
answer.

Wim

=COUNTIF(A1:A10,"*i*")


--ron

 

[Wim]

No, that formula only checks whether the cell is exactly the same. In
your suggested situation: only cells that are EQUAL to "i" (the result
in my 3 cells would be 0!), not those cells that CONTAIN "i" (the
result in my 3 cells would be 2!). I hope I make myself clear?
Wim

 

[Roger Govier]

Hi Wim

Did you try Ron's formula?
I think you will find that he has given you an accurate solution to the
question you posed. Go on, just try it and see!!!

Regards

Roger Govier

 

[Peo Sjoblom]

No you don't make yourself clear, why don't you have the courtesy
of trying the formula first. Don't know if you ever heard of wildcards but
that is what * is, it means that anything with i in it will be counted
The formula works

 

[Ron Rosenfeld]

No, that formula only checks whether the cell is exactly the same. In
your suggested situation: only cells that are EQUAL to "i" (the result
in my 3 cells would be 0!), not those cells that CONTAIN "i" (the
result in my 3 cells would be 2!). I hope I make myself clear?
Wim

You obviously did not even bother to try the formula as posted.

In case your eyesight is off:

"i" is NOT THE SAME AS "*i*"

Can you tell the difference? If not, show it to someone knowledgeable.

--ron

 

[Wim]

Sorry sorry sorry, I hadn't seen your ********. That definitely did the
trick!
Thanks so much!

Again my apologies. (but please no sarcasm next time: "did not even
bother", etc. Was that sarcasm triggered by my phrase "I hope I make
myself clear?". Sorry for that, was not meant to be rude at all.
Perhaps a result of English not being my mother tongue...)

 

[Ron Rosenfeld]

Sorry sorry sorry, I hadn't seen your ********. That definitely did the
trick!
Thanks so much!

Again my apologies. (but please no sarcasm next time: "did not even
bother", etc. Was that sarcasm triggered by my phrase "I hope I make
myself clear?". Sorry for that, was not meant to be rude at all.
Perhaps a result of English not being my mother tongue...)

It was triggered by your rejecting the formula without even trying it out.
--ron