Formula needed: count if cell CONTAINS certain text

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

0
eisingspam (27)
12/7/2005 12:28:36 PM
excel 39879 articles. 2 followers. Follow

7 Replies
777 Views

Similar Articles

[PageSpeed] 30

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
0
ronrosenfeld (3122)
12/7/2005 12:49:42 PM
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

0
eisingspam (27)
12/7/2005 1:24:17 PM
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
> 
0
roger1272 (620)
12/7/2005 1:41:55 PM
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
>


0
terre081 (3244)
12/7/2005 2:08:38 PM
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
0
ronrosenfeld (3122)
12/7/2005 2:19:14 PM
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...)

0
eisingspam (27)
12/14/2005 9:02:24 AM
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
0
ronrosenfeld (3122)
12/19/2005 5:06:10 AM
Reply:

Similar Artilces:

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

Event ID: 8270 ,LDAP Error--Need help to fix it..
Event Type: Error (win2000 Srv+exchange2000) Event Source: MSExchangeAL Event Category: LDAP Operations Event ID: 8270 Computer: Exchange_2000_Server_Name Description: LDAP returned the error [b] Administration Limit Exceeded. dn: "GUID=8F37136AFCEE0B41BDD353B78F8E6067" changetype: Modify mail:USER1@Domain_Name.Domain_Suffix proxyAddresses:MS:NET/PO/User1 : CCMAIL:User1, PO at NET : SMTP:USER1@EMAIL.Domain_Name.Domain_Suffix : X400:c=US;a= ;p=Exchange_Org_Name;o=Exchange_Site_Name;s=Last_Name;g=First_Name; : smtp:USER1@MAILLIST.Domain_Name.Domain_...

Recognizing misc. text
I have this worksheet in which a collumn contains a lot of text but i want to filter out the cells that contain the text "SAP (miscellaneous text)" from the rest. Is there any way excel can recognize the SAP+space part and doesn't care about what follows after that in the cell as long as there at least is some text? Thx in advance. -- MeisterHim ------------------------------------------------------------------------ MeisterHim's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27401 View this thread: http://www.excelforum.com/showthread.php?threadi...

crm consultant needed asap
i am looking for a crm consultant who has a lot of experience with form customization, crm implimentation, heavy work flow, and activities. ideally someone in arizona but not required. telecommute will be considered for the right person. looking for someone for a possible 2 month (i am guessing here....) project. if you are available and have this experience please email me at j-e-f-f@mag-en-ta-tech.c-o-m (remove the -). we are looking for someone to start like next week at the latest (this message was posted 08-08-2004). This message was posted 8/4, not 8/8. And I have some swamp land in f...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

pulling certain characters from a string of text
I need to look up "certain critera" within a string of characters, then return that "certain criterea" to a new column. Some examples of a strings of characters may look like these: K5J091509001 Sample PO#S881009 K55sample PO CarrieRJR TJ5 My "Certain Critera" I have listed on another sheet, named "REP ID" K5J S88 K55 RJR TJ5 How do I pull out the 3 characters of "Certain Criterea" from the string of text and copy or enter it into a new column? Hi, =left(a1,3) "SaraMack" wrote: > I need to look up "c...

can I snap wrap points to a text box
rather than having to add individual wrap points to the edge of a frame, which is never as accurate anyway, can they be set to 'snap' to a frame (eg the ellipse) so that they are perfectly inline, (and which would of course be a lot quicker)? Edit points will not snap. There are options for edit points, select a point, right click. If you hold down control, the cursor will turn into an x, you can delete a point with a click. Truly a good draw program would be preferable. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com &q...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

cannot ping certain workstations through hostnames
Hello We have a problem where certain pcs cannot be pinged through a VPN session. Please note that other pcs that are in the same DHCP scope can be pinged from a workstation connected to the VPN. I have tried to change the IP setting on the affected workstations to NetBios over TCP/IP, but that didn't work. The local windows firewall is disabled on all pcs (its set this way in group policy), so that isn't an issue. I have flushed the DNS tables on the "problem" pcs, but that didn't work either I am at a loss here....anyone have any thoughts? Something in...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

default text height comment
Is there a way to set the default text height for a new comment? Thanks mark (I've looked through help but can't find it if it's in there.) I assume you mean the font size? There is no text height available in Excel. A comment has a shape property and that is what you can use to change the font size. They didn't make it easy ... Range("D4").Comment.Shape.TextFrame.Characters.Font.Size = 12 -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (free and commercial excel programs) "mp" <nospam@Thanks.com> wrote in message...

Removing spaces from text #4
I'm in excel and i have a bunch of text data that has an extra space at the end of the text on the right hand side for each cell. Is there any easy way to remove this space? Use the TRIM() function. -- Kind regards, Niek Otten "lj" <lj@spu.edu> wrote in message news:1144876429.220961.309040@j33g2000cwa.googlegroups.com... > I'm in excel and i have a bunch of text data that has an extra space at > the end of the text on the right hand side for each cell. Is there any > easy way to remove this space? > I tried using that function but the results st...

Change the text of a shape rather than its master
Hi, I build custom masters by mixing two general shapes, say square and circle together, and have text on both the shapes. But after I drop an instance of the master into a page, I cannot modify the text of the instance. To do so, I need to modify the text on the master, which is non-sense for me. How to change the text of a shape without modifying its master? Thanks! How are you doing this? By code or by the UI? Are you grouping the shapes? If you drag two shapes to the stencil, it will group the shapes. So instead of a square and a circle you have three shapes. A Square, Circle and the...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...