if cell starts with characters formula

Hi

I need to count cells in a column starting with certain characters. each
cell's data varies in length. I have tried with @countif( but does not work
if the cell contains other characters after the "prefix".
eg.

row 20 cell 5     apples 
row 21 cell 5     apples red
row 22 cell 5     apples green
row 23 cell 5     plums  green
row 23 cell 5     plums  purple

totals required for apples    =   3   (regardless of colour)
total required  for plums     =   2   (regardless of colour)

@countif(C20:c30,"plums")   gives answer of 1 require answer of 2
@countif(C20:c30,"apples")  gives answer of 1 require answer of 3

Thanking you in advance

Regards

Norman

-- 
Message posted via http://www.officekb.com
0
forum (466)
3/24/2005 9:25:17 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
294 Views

Similar Articles

[PageSpeed] 50

Hi Norman

use the COUNTIF function
e.g.
=COUNTIF(C20:C30,"*apples*")

Cheers
JulieD


"Norman Kong via OfficeKB.com" <forum@OfficeKB.com> wrote in message 
news:72bab8bbfa3a475e8d4b7a72116d0789@OfficeKB.com...
> Hi
>
> I need to count cells in a column starting with certain characters. each
> cell's data varies in length. I have tried with @countif( but does not 
> work
> if the cell contains other characters after the "prefix".
> eg.
>
> row 20 cell 5     apples
> row 21 cell 5     apples red
> row 22 cell 5     apples green
> row 23 cell 5     plums  green
> row 23 cell 5     plums  purple
>
> totals required for apples    =   3   (regardless of colour)
> total required  for plums     =   2   (regardless of colour)
>
> @countif(C20:c30,"plums")   gives answer of 1 require answer of 2
> @countif(C20:c30,"apples")  gives answer of 1 require answer of 3
>
> Thanking you in advance
>
> Regards
>
> Norman
>
> -- 
> Message posted via http://www.officekb.com 


0
JulieD1 (2295)
3/24/2005 9:31:32 AM
It works !!!
Thanks

-- 
Message posted via http://www.officekb.com
0
forum (466)
3/24/2005 9:49:52 AM
you're welcome - thanks for the feedback

"Norman Kong via OfficeKB.com" <forum@OfficeKB.com> wrote in message 
news:8e1689c9476249afb84caa61e8a7fe6f@OfficeKB.com...
> It works !!!
> Thanks
>
> -- 
> Message posted via http://www.officekb.com 


0
JulieD1 (2295)
3/24/2005 10:18:24 AM
Reply:

Similar Artilces:

CRM do not recognize different character set encoding for incoming e-mail???
I have a problem with some of incoming e-mail. If incoming e-mail character set is not unicode but for example Cyrillic then I dont see russian letters, but some scrawl!!! My Outlook recognize all what I need. Whats about CRM??? Sorry about my poor english. ...

SCHTask.exe returns "Failed to start" from SVR 2008
Hi, We have a number of 2003 servers which run lots of scheduled tasks around the clock. We also have some 2008 servers which do the same. We use SCHTask.exe from the 2008 servers, to query the scheduled tasks on the 2003 servers, and then look through the output of this query for errors, which is then used elsewhere to alert us that a scheduled task has failed on a given machine, and what the task is called. The problem we are having is that if a scheduled task runs sucessfully on a 2003 server, but has a NON ZERO return code, when SCHTask is run from a 2008 server and quer...

Formulas not updating ?
I have a problem with my spreadsheet under Excel XP. The problem has to do with cell references not updating when a row is inserted into the worksheet. The formulas in the cells are relatively simple: =B13 and =DATE(YEAR(B10),MONTH(B10),DAY(B10)+7) I can copy a row and insert it elsewhere or just insert a blank row and the cell references in rows below are not updated in all cases. It seems that rows that are separated from the inserted rows by a blank row are updated. Is this a known issue or does anyone have any suggestions on how to fix this? The spreadsheet is intended to p...

Wrapping text in a cell
In a single cell, suppose I want text to appear on two lines. Viz: Case One Case Two How do I do that so that I specify the wrap point? Thanks! If you are typing the data into the cell use Alt-Enter between each string to indicate where you want a line break to occur. Case One<Alt-Enter>Case Two Alt + Enter -- Lilliabeth ------------------------------------------------------------------------ Lilliabeth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27741 View this thread: http://www.excelforum.com/showthread.php?threadid=476428 If you...

Locking Formulas to Cells
I don't know if I am using the correct terminology but this is what want to do: I have placed formulas in multiple columns that calculat my sales numbers for a bid. The problem I am running into is that change the bids for every person and when I clear a cell is clears th formula from it as well. Is there a way for me to clear cells withou deleting the formula I have placed inside it? I know that I can jus grab the first cell in the column and drag it down to re-load th formula in that column but I don't want to have to do that. I want th formulas permenant and the data I enter...

How do I add a hyperlink to an individual word in an Excel cell?
I am using Excel 2000 (not by choice) and I need to add a hyperlink to an individual word within the cell, not the whole cell itself. For example in the sentence "Click here or here to go to the appropriate web page." I want the words "here" to each have a separate hyperlink. Any ideas? I can manage some VBA too if necessary. Thanks, Rosalie Hi Rosalie, You can't do that in Excel. You would have to use HTML or Word or some other means. You could fake it, the entire cell would be a link, but you could after assigning the hyperlink select another...

how can I drag formulas with other cell references
example cell A1 contains formula "=stdev(a2:a7) cell a2 contains formula "=stdev(a8:a13) Is it possible to drag the formula down so that cell a3 contains "=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5, a6, a7 etc. etc. Hi Bram, See http://www.mvps.org/dmcritchie/excel/snakecol.htm#snkAddr B1: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) B2: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) It was easier to test and verify using SUM instead of stdev, but the formula is same NOTE the formulas are in a different colu...

Cell Selection?
I have a colum of numbers 198 0 0 198 2 What I need, is to be able to select 3. the 198's are not going to be used in the next part of my equation. That seems simple enough, however all the numbers could be usable (not 198) and I need to use just the first three. Any ideas? One interp / way, using non-array formulas Assuming source numbers in A1 down Put In B1: =IF(COUNT($C$1:C1)>3,"",C1) In C1: =IF(ISERROR(SMALL(D:D,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0))) In D1: =IF(A1="","",IF(A1=198,"",ROW())) Select B1:D...

Formulas don't work in certain cells #2
nope, the cells are formatted as numbers. I simply cannot figure thi out. -Jorda -- kalik24 ----------------------------------------------------------------------- kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View this thread: http://www.excelforum.com/showthread.php?threadid=27423 Being formatted as numbers doesn't automatically mean they are numbers. Copy an empty cell formatted to General. Select your range of numbers and Paste Special>Add>OK>Esc Gord Dibben Excel MVP On Mon, 1 Nov 2004 11:59:46 -0600, kalik247 <kalik2...

Changing Titles by a formula
From cell A1:A10 I have standard titles that are linked to another sheet. I only need to change the office and date per each title. Is thier a way to do that making the office and date a formula in another cell? example of title ABC Comp, Office 38L Sales as of 6/07/2004 I need to only keep changeing the 38L and date. Any suggestions----Thanks Say Office # is in Column 'C' in cell A1: ="ABC Comp, Office "&C1&" Sales as of "&Today() ----- Heather wrote: ----- From cell A1:A10 I have standard titles that are linked to ...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

Start Outlook 2003 at windows startup??
Hi, I have a simple question: how do I have Outlook 2003 started automatically and minimized to system tray at windows startup? Thanks in advance edomonet@libero.it <edomonet@libero.it> wrote: > how do I have Outlook 2003 started automatically and minimized to > system tray at windows startup? Put a shortcut to it in your Startup program group. However, I don't believe it will start minimized. -- Brian Tillman It's better to put a shortcut in your Quick Launch bar or you will get errors if OL loads too soon before your connection and antivirus. You can customiz...

sorting formulas
Hi, Ive made a spreadsheet which all the cells accept titles contain look up formula's. I want to be able to do sorts on the columns but it doesnt seem to work. I think its because excel recognises the content of the cells as a formula, but i want it just to see the result of the look up and sort that. Is there anyway to solve this problem? Thanks to all who help. Matt -- m4tt ------------------------------------------------------------------------ m4tt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25835 View this thread: http://www.excelforum.com/s...

if cell in other worksheet meets criteria, then leave blank.
I have data in worksheet A for each month, for each entity. Then I have a summary page which shows just the averages for each entity for each month. Then I need to create a master summary page which shows just the annual average for each entity, just one line per entity. My Summary page shows Jan-Dec in column A. I have a formula averaging the numbers from worksheet A. Jan-Mar is done. But the rest of the year hasn't happened yet, so April's formula results "#DIV/0!". I have already put in all the formulas for the year to be done with it. But I don'...

need to make a formula that would add a field value to current dat
I have made a form in which I input different values. On of the values is (How Many Days). Now I need to a assign a default value, or expression (not sure which way to go about this) that will take the date value for (Date) and add the value (How Many Days) I figured that the formula should read =sum([Date]+[How Many Days]) But that is not giving me any results, thanx for your help in advance =DateDiff("d", Date(), [How Many Days]) -- Wayne Manchester, England. "J Man" wrote: > I have made a form in which I input different values. On of the values is ...

Steps to start over
If my data is hopelessly screwed up, is there a way to start completely fresh? Would it be uninstall/install? I don't know how to re-install, since I installed from a Trial copy off the web and then paid for the upgrade. There is a webinst.exe file in the money account, but I don't know what it does and am afraid of losing my money. (The cash I paid... not the application.) I tried deleting the account and re-entering. That didn't help. I restored from backup, but the backups don't go back far enough to precede my problem. In microsoft.public.money, Laurel wrot...

Fill cells with interpolated values
Hi What is the easiest way to fill cells with linear interpolated values ? e.g. i have value 5 in cell A1, and value 15 in cell A6. Cells A2 ... A5 should now be filles with 7, 9, 11, 13. of course, it's not a big deal to write a formula for interpolation, but maybe there is more simple way, (just by some mouse clicks....?) Biff Select the range A1:A6 with your start and stop value in their respective cells, and then do Edit / Fill / Series / Trend / Linear -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/...

Changing of range (Address) to (Cell)
Hi people I have encounter a problem with the use of range From a recorded macro, it's listed this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2,A24:M28"), PlotBy:=xlRow And I edit it to this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2," & Cells(StartX, StartY), Cells(LastX, LastY)), PlotBy:=xlRow And obviously VBA compiler won't let me go this easily, it happen to give an "evil-comment" on my source range, May i know how can i solve this Thank You Hi Kaiyang, Try this, assuming the...

unwanted french characters in OneNote
Whenever I type in punctuation marks like ' " ? in Onenote, instead of the punctuation marks, I get accented french characters. It doesn't appear to be a problem in other word applications (or even here, in this forum) How do I fix this? I've tried resetting the language in OneNote and the Keyboard, but it doesn't appear to help. Thanks How about you only post in the relevant newsgroups next time. This is in the OneNote group where it belongs. Regards Judy Gleeson MVP Outlook www.judygleeson.com www.deskdoctors.com Want to be more productive? Outlook 2003 user...

Extracting the month that a date refers to in another cell
In cell A1 I have the date 3/15/2003 (no formatting). In cell B1 I have the formula =year(A1). The year shows correctly as 2003. In cell C1 I have the formula =text(weekday(A1),"ddd"). The text shows as "Sat" which is correct. In cell D1 I have the formula =month(A1) which correctly shows '3'. I attempted to use =text(month(A1),"mmm") in cell D1, but it shows as "Jan". Can someone tell me what I am doing wrong? TIA, Alan =TEXT(A1,"mmm") for 3-letter month name and =TEXT(A1,"ddd") for 3-letter day name. where A1 houses a...

How to add a drop down menu to a cell
How to add a drop down menu to a cell? hi, you don't add it to a cell. It's a control in the toolbox and it sits on top of the sheet. Tools>customize>toolbar tab>select control toolbox. it's called a combo box. click it and drag it to the sheet. for more help on combo boxes, type combo box in help. >-----Original Message----- >How to add a drop down menu to a cell? >. > You can do it. Take a look at Data>Validation, see this site for full details http://www.contextures.com/xlDataVal01.html -- HTH RP (remove nothere from the email address if mailing d...

cant get the mta stack to start
im trying to get my mta stack services to start, but it wont what can i do. Is this the first time you have tried to start the services on this server, or is it a problem on aserver that has been running for a while? If it's a new server, have a look at this article: http://support.microsoft.com/?kbid=840470 Probably the best place to start is to see what information is in the application event log. If you need to, you can also turn up the level of logging to see further information. To do this, start ESM then right-click the server name and select Properties. On the diagno...

Show formula result in another sheet
sheet1 A:A contains a formula with some cells returning a value sheet2 A:A references sheet1 A:A with a simple formula: =sheet1!A1 above formula filled down in sheet2 A:A the problem is it is not showing the results from sheet1 anyone know why that should be? Sorry false alarm, I had calculation set to manual. Results are coming back as they should. Red faces all round. "Gotroots" wrote: > sheet1 A:A contains a formula with some cells returning a value > > sheet2 A:A references sheet1 A:A with a simple formula: > > =sheet1!A1 > ...

Day names in Calendar weekly view cells.
How does one display the days of the week, as well as the dates, in the title bars of the date cells in Weekly View of Outlook 2003 Calendar? It seems that an 'alternate calendar' of weekday names would do the trick if there's no simple setting that I've missed. Thanks, -- Michael H ...

Importing Data That Contains super- and sub-script characters
In wanting to build a "glossary" type of database I realize the problem of entering phrases that contain subscript and/or superscript characters. Yuck! How is this handled by the pro's? Thanks, -- tbl Hi tbl, RTF - rich text format - is a way of encoding formatting text in a string. You'll need to use rich text controls on your forms, and probably store the rich text in memo field. There are many rich text controls around, not all of which work reliably on Access forms. Those that do include Stephen Lebans's (free download from http://www.lebans.com/richtext.htm) a...