# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  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

On 7 Dec 2005 04:28:36 -0800, "Wim" <eisingspam@iafrica.com> wrote:

>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


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

----------


## 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

--

Regards,

Peo Sjoblom

"Wim" <eisingspam@iafrica.com> wrote in message
news:1133961857.462179.239880@g49g2000cwa.googlegroups.com...
> 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
>

----------


## Ron Rosenfeld

On 7 Dec 2005 05:24:17 -0800, "Wim" <eisingspam@iafrica.com> wrote:

>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

On 14 Dec 2005 01:02:24 -0800, "Wim" <eisingspam@iafrica.com> wrote:

>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

----------


## msensor

Hey Ron,

what steps should I take in order to scan one cell for multiple words?  I need a formula to look for 2 - 3 words in a cell 
and count each cell that contains these words.  Furthermore, is it possible to use this formula or a similar one to qualify 
and disqualify different words in a cell in order to find a count?

----------


## Cutter

Hello msensor, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules.  Please read the following and make the necessary change.  Thanks.

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- *start your own thread*. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

----------


## RMoses

I'd like to take the COUNTIF function to the next level.  I have an Excel 2010 workbook that contains 33 worksheets.

The first worksheet is 'Summary'
The last worksheet is 'Template'
The remaining 31 sheets are for each day of the month.

I have a 3D function on 'Summary' that counts the total number of entries across all sheets: 

                =SUM(Summary:Template!G1)

and it works great.

I have a function on each page that counts occurrences of specific text values:

=COUNTIF(G4:G40,"UHC")

Again, it works great.

What I would like to do is have a 3D function on 'Summary' that counts the text values.  I realize that I could just add up all the COUNTIF values, but I'd rather have a combined function in just one place, that being  on 'Summary'.

I tried =SUM(Summary:Template!COUNTIF(G4:G40,"UHC")) , but I get very useless error message from Excel.

Is it possible to do what I want?

Thanks.

Rick

----------


## arlu1201

RMoses,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do.  Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

----------


## RMoses

Moved to http://www.excelforum.com/excel-gene...d-countif.html

----------

