Formula needed: count if cell CONTAINS certain text

W

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
 
R

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
Click to expand...


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


--ron
 
W

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
 
R

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
 
P

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
 
R

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
Click to expand...

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
 
W

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...)
 
R

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...)
Click to expand...

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