Count cells that Contain text

Is there a way in excel to count the number of cells in a colum tha
contain a specific piece of text, e.g. "ACT"

I know that I can use the countif or sumproduct functions to coun
cells which *only* contain "ACT" but I need to count the number o
cells in a column which countain ACT but may also contain other text a
well.

You can manually do it using autofilters but I need to count it using 
formula.

Any help would be greatly appreciated.

Ta

And

--
Message posted from http://www.ExcelForum.com

0
8/4/2004 1:01:34 PM
excel 39879 articles. 2 followers. Follow

1 Replies
920 Views

Similar Articles

[PageSpeed] 13

=COUNTIF(Range,"*ACT*")


-- 

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"andibevan >" <<andibevan.1agt8x@excelforum-nospam.com> wrote in message
news:andibevan.1agt8x@excelforum-nospam.com...
> Is there a way in excel to count the number of cells in a colum that
> contain a specific piece of text, e.g. "ACT"
>
> I know that I can use the countif or sumproduct functions to count
> cells which *only* contain "ACT" but I need to count the number of
> cells in a column which countain ACT but may also contain other text as
> well.
>
> You can manually do it using autofilters but I need to count it using a
> formula.
>
> Any help would be greatly appreciated.
>
> Ta
>
> Andi
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre08 (1112)
8/4/2004 1:34:09 PM
Reply:

Similar Artilces:

How can I hide letter in a cell?
I am trying to hide a letter in a cell, and when the cell is filled in with black, then have the letter appear. I am doing this on a reservation form. So make the text white? That way, if the cell is filled black, it'll show. ******************* ~Anne Troy www.OfficeArticles.com "Linds" <Linds@discussions.microsoft.com> wrote in message news:3F92F52E-1789-4872-B7D3-020AAB481F64@microsoft.com... > I am trying to hide a letter in a cell, and when the cell is filled in with > black, then have the letter appear. I am doing this on a reservation form. > Chang...

In cell editing
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel What is the Mac equivalent for ALT+ENTER to start a new line in the same cell, please? option+command+return <br><br>Bobby and <br><br>option+command+enter <br><br>Bobby ...

changing text to numeric
i have two table. one is PO table and one is DI (delivery instruction) table. i have make a form and using DI table as the control source. then i make a combo box to look up the PO no. that combo box i'm using the PO table. cos i want to look into the PO table to see which PO i want to call in the material from the supplier. after select the PO no from the PO table i will keep the data in the DI table. PO no from the PO table was change from number field to text field when in the DI table. can i make the PO no as number field in DI table? how? cos i need using the PO no which is in ...

what command shifts all cells in a column up or down?
Accidentally did something in an Excel worksheet to shift all cells in a particular column down. Do not know the command(s) or keystrokes to reverse what I did and shift all cells in the column up. select the inserted cell Edit>Delete when asked, click "Shift cells up" -- Kind regards, Niek Otten "marco" <marco@discussions.microsoft.com> wrote in message news:C0CAFF80-291E-4A62-9D52-980D94F74378@microsoft.com... > Accidentally did something in an Excel worksheet to shift all cells in a > particular column down. Do not know the command(s) or keystrokes...

page count
thank you so much for adding page count in the online clipart section! It helps me to decide how deep I want to go in my search! This was a feature a lot of people missed. The clipart page is faster... This is what Eric Lawrence wrote in the Design Gallery newsgroup. I just wanted to let everyone know about some long-awaited changes that we made to the Office Online Clip Art website this afternoon. Clip Art paging is back! You can now see how many results your search returned, and jump to a specific page. This was our top feature request for many months now, and it's finally here. ...

Replacing cell with text
Hi, I want to know if it is possible to do the following: Imagine I have a legend in which I say which text corresponds to whic letters ex: John -> J Harry -> H Susan -> S and what I want to do is when entering J, H or S in a cell, it wil change to the corresponding text. Is there a way of doing it without having to run a macro ? Thanks in advance Pedr ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Go to Tools > AutoCorrect Options (XL2002) and insert new...

How do I make numbers become text?
I am trying to create a spreadsheet where numbers entered in one location become text in another. I tried the Help option, but I am still lost. Please help, and thank you. From earlier postings: How to Convert a Numeric Value into English Words http://support.microsoft.com/default.aspx?scid=KB;EN- US;140704& and: (courtesy of a cut and paste from a Tom Ogilvy post): If you want an addin that provides a worksheet function that does this, download Laurent Longre's free morefunc.xll addin found here: http://longre.free.fr/english/ It is downloaded in a zip file which also conta...

Miscellaneous words appear in Excel 2002 cells
I am working on an Excell 2002 workbook and when I put my cursor in a cell , spurious words get entered into the cell e.g. "and thee the and ...." is one example. I have cable modem and it feels like "someone" has taken control of my PC - by the way I have also noticed this hapening in MS Word but is not as bad as Excel - can some one help. thanks KK Have you enabled speech tools? Click Tools / Speech and examine your options. /i. "KK" <anonymous@discussions.microsoft.com> wrote in message news:061d01c39d1c$36fc85f0$a601280a@phx.gbl... > I am w...

Format a cell in a range of cells based on value of another cell
In ExceI 2010, I want to highlight those cells in a range of cells which are less than the value of another cell. I have tried several formulas and nothing seems to work. Example formulas applied to the range of cells in K5:K94: =<$G$1 "=<$G$1" ="<$G$1" =<"$G$1" Any help would be appreciated, Barney On 12/07/2011 14:30, Barneypo@gmail.com wrote: > In ExceI 2010, I want to highlight those cells in a range of cells > which are less than the value of another cell. I have tried several > formulas and nothing seems to work. > > Exa...

Sum Based on Cell location in Column
An Excel dilemma... I have a sheet that is updated from another program and it fills the column with numbers that start randomly based on time somewhere between 1 and 600 rows... that is... the data can start at row 256 and continue to 600 or at any other row. I would like to SUM the first 30 Rows when the data starts Automatically coming in. Example First Update Second Update Row Column Row Column 1 0 1 0 2 0 2 0 3 24 3 0 4 36 4 0 5 42 5 20 6 55 6 ...

Text Box 05-21-10
I have the following event procedure on a text box: Private Sub txtNotesOnTransaction_BeforeUpdate(Cancel As Integer) Debug.Print "User: " & CurrentUser() & " " & Me!txtNotesOnTransaction End Sub It is suppose to stamp the user name in the text box but it does not work. Any suggestions? Thanks PJ - The debug.print will just display (in the immediate window of the code pane) the name and the Notes. If you want the notes saved with the name, do this: Me!txtNotesOnTransaction = "User: " & CurrentUser() & " " &a...

Drag and drop without changing the format of the cell left behind
I would like to move text from one cell to another without losing the format of the cell I draged it from, or have the format auto revert back after I drop the cell. --- Message posted from http://www.ExcelForum.com/ I don't know of a way to do this. Why not CTRL-Drag (copy/drag)? It'll keep the original contents in the originating cell along with the formatting. jeff >-----Original Message----- >I would like to move text from one cell to another without losing the >format of the cell I draged it from, or have the format auto revert >back after I drop the cell. >...

changing text case
Is there a way to change the text case in Excel? Ex. from all caps to Sentence case, etc. See: http://www.officearticles.com/excel/change_the_case_of_text_in_cells_in_microsoft_excel.htm ************ Hope it helps! Anne Troy www.OfficeArticles.com "Linda B" <LindaB@discussions.microsoft.com> wrote in message news:A0BCD63F-3E4F-4F18-9FFD-8310E5840C8A@microsoft.com... > Is there a way to change the text case in Excel? > > Ex. from all caps to Sentence case, etc. Excel supports conversion to upper, lower or proper case, but it has no sentence case. I created this ...

counting rows
I suspect this is relatively easy but I'm not sure how to accomplish this. Is there a way to count the "non blank rows" in a specific column. We need to know the number of rows that have been filled in within each column....thanks You can use the Counta function. =COUNTA(A1:A1000) This counts any cells within the range that have data in them. Hope this helps Judith >-----Original Message----- >I suspect this is relatively easy but I'm not sure how to accomplish this. Is there a way to count the "non blank rows" in a specific column. We need to kno...

Spreadsheet name in a Cell
I am looking for a cell formula that would recover the spreadsheet name and display it in a cell - more or less as "=CELL("row";A1) function is able to return the line number of cell A1 or as "=CELL("filename";C18)" will return the full path of the Excel workbook housing cell C18... Thanks a lot - Alain79 See http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "Alain79" <desaivresanospam@free.fr> wrote in message news:ddsce3$94f$1@s1.news.oleane.net... > I am looking for a cell formula that would recover the spre...

How do I "cut" only the data from a cell
Using Excel 2000. I have 2 sheets in my work book. Cell A1 in Sheet 2 has a simple formula: "=Sheet1!A1". Thus the data in sheet 1 A1 is always replicated in Sheet 2 A1. However, if I "cut" Sheet 1 A1 and "paste" somewhere else in the book, Sheet 2 A1 still reads the original data in Sheet 1 A1 and the formula in Sheet 2 A1 has changed to refer to the new location of the pasted data. Normally, this is not a problem but in this case I want the data in Sheet 2 A1 to read whatever is in Sheet 1 A1 at all times. I do not want the formula to change in any circum...

How to fit a row height to a cell contents automatically?
Hello How to fit a row height to a cell contents automatically? Double clicking on the row edge doesn't decrease height. There's free space left below text. This happens to some rows only. -- Please, inform me of your reply: click Message menu, then 'Reply to all' in Outlook Express (this sends your reply to the newsgroups and to my email address at the same time) or inform me by sms to 79163876746 from www.mts.ru/sms or to my ICQ#135557327 or by messenger to kopnichev@hotmail.com Mr. Dmitriy Kopnichev e-mail: kopn@hotbox.ru Select whole row and set font size to, say, 6. R...

#REF! with cell value check
Hello, I am trying to create a formula with the INDIRECT or OFFSET command so my cells will reflect the cell at the address I am calling on instead of the one I deleted. My formula also needs to show the result of another cell if there is something in the cell and nothing if the cell is blank. Are there any Excel Wizards out there who can help? I have tried several things with the #REF! as my result. I am using Excel 2003 and my formula I am trying to modify with INDIRECT is: =IF(AND(BOM!A26>1,TAKESTOCK!A16<>""),BOM!$D$5,"") FYI: BOM is my first sheet...

Counting with conditions based on another range
HI. I've tried several different ways now to the experts :) I have two rows of data: Emp Lvl Rating 1 4 2 3 2 2 1 4 1 2 2 3 I need to count How many emp lvl 2's with rating 2 = 1 what is the formula for this? Try this: =3DSUMPRODUCT((A1:A6=3D2)*(B1:B6=3D1)) Hope this helps. Pete On Nov 27, 9:25=A0am, stephanie <stepha...@discussions.microsoft.com> wrote: > HI. =A0I've tried several different ways now to the experts :) ...

Contents of cell in footer #2
I want the contents of a specific cell to be in a footer, can I do this? This code, placed in the ThisWorkbook code module, automatically picks up the cell value when printing. Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.LeftFoot�er = Activesheet.Range("A8").Value End Sub -- HTH RP (remove nothere from the email address if mailing direct) "dbhenkel" <dbhenkel@discussions.microsoft.com> wrote in message news:6952C152-C970-45CD-A9BC-126C7645C389@microsoft.com... > I want the contents of a specific cell to be in a footer, can...

Dates changed in cells?
I have Excel 2000 version 9.0.2720 running in WinXP Pro. I started creating a table and in some of the cells I entered in dates. Everything was fine. Everything printed fine. I saved the file. The next day I open up the file in Excel and found that all my coulmns had resized to variuos widths AND,....the cells with dates in them changed to curious numbers. Example: 7/24/2003 has become 37826. If I click on the cell the formula toolbar shows the correct info but the cell doesn't. If I double click the cell it converts to the date I typed, but as soon as I click out of the cell it chan...

Importing Tab Delimited Text File to an Existing Table in Access
Office 2007, just upgraded from 2000 ( OMGOODNESS!! What a flippin' NIGHTMARE!!) I am simply trying to import a tab delimited text file into an existing table in Access. In Office 2000, I was given an opportunity to change the data type of columns to be imported during the import process itself. In 2007, I am not given an option and end up with import errors for every single row of data (over 1.5 million rows) because it reads a text field of numbers as a number datatype. I can find no documentation on how to solve my problem as Microsoft only provides information for...

count unique entries
helllo again, i a have a "Y" column of date entry. Most of dates are repeated, some are entered once. I need to count only the number of dates (excldg repetitions). example 15-Sep-09 22-Sep-09 20-Oct-09 20-Oct-09 20-Oct-09 22-Oct-09 From above 6 dates; the unique count should be 4. thanks Try the below =SUMPRODUCT((Y1:Y100<>"")/COUNTIF(Y1:Y100,Y1:Y100&"")) If cell Y1 is header then adjust the range as Y2:Y100 If this post helps click Yes --------------- Jacob Skaria "driller2" wrote: > hell...

Questions on copying from one sheet to the other and coping every other cell.
I need to change a formula on sheet two reading... =OFFSET($B$1,0,ROW()-2) which I drag down the column and it copies text like this =B1 =C1 =D1 etc. I need to make it every other cell in the row instead of every cell. For instance now when I drag it, it will go down the column and copy text from =B1 =D1 =F1 +H1 Etc.... Second, first time ever but the columns in first row in sheet 2 is full, so I also need to continue same formula on the next sheet, sheet 3 but have it continue copying to same column on Sheet 2. How do i make this possible? Thanks! TKL -- KatyLady ---------------...

sum of cells within a month...
is there a way to do this; i would like to add the values in a range of cells based on the dates of another range of cells. for example: 6401936 4/17/2003 G025498 21,500.00 28860 5/9/2003 6425598 4/23/2003 G025852 32,650.00 28860 5/9/2003 6401938 4/17/2003 G025498 21,500.00 28860 5/12/2003 6430847 4/24/2003 G025999 35,150.00 28860 5/12/2003 6430849 4/24/2003 G025999 21,950.00 28860 5/12/2003 6489337 5/8/2003 QC03044 21,500.00 28860 5/12/2003 6437699 4/25/2003 G026497 21,500.00 28860 5/27/2003 6503075 5/12/2003 G025252 25,500.00 28860 5/27/2003 ...