Formula needed: count if cell CONTAINS certain text

Wim · 12-07-2005, 08:30 AM

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 · 12-07-2005, 08:55 AM

On 7 Dec 2005 04:28:36 -0800, "Wim" <[email protected]> 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 · 12-07-2005, 09:25 AM

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 · 12-07-2005, 09:45 AM

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 · 12-07-2005, 10:15 AM

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" <[email protected]> wrote in message
news:[email protected]...
> 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 · 12-07-2005, 10:25 AM

On 7 Dec 2005 05:24:17 -0800, "Wim" <[email protected]> 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 · 12-14-2005, 05:10 AM

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 · 12-19-2005, 01:10 AM

On 14 Dec 2005 01:02:24 -0800, "Wim" <[email protected]> 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** · 09-07-2012, 01:48 PM

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** · 09-08-2012, 08:28 AM

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** · 08-01-2013, 06:11 AM

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** · 08-01-2013, 07:23 AM

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.