If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#52
|
|||
|
|||
Can I Use a Count Function for Text?
=countif() will count the number of cells that match the criteria.
If you want to count the number of @'s in a range: =SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"@",""))) Adjust the range to match, but you can't use the entire column until xl2007. Paula Ohio wrote: Joan, I have the @ character in the same cell more than once and when I use the =COUNTIF(A1:A100,"*@*") that Bob Phillips recommended, I get a count of five instead of 10 in my test text. For example, , on five different rows in Excel should count 10 @ chars. "Joan NYC" wrote: Aha... Sumproduct is not a function I ever tried I have a column of text. Some cells contain the character "@" with a space preceding the rest of text in the cell. I realize the @ could be considered an operator so it is preceded with an apostrophe to designate text. I want to sum the cells that have the "@" Thanks "David Biddulph" wrote: You'll need to be a bit clearer as to what you're trying to do. If COUNTIF or SUMIF doesn't do the job, you may want to try SUMPRODUCT. -- David Biddulph "Joan NYC" wrote in message ... I have been trying to calculate a column of Text in order to sum contents by certain criteria I have tried Count, CoutA and CountIF and cannot be able to get anything to work. Am I barking up the wrong tree? Can this be done? Thanks much. -- Dave Peterson |
#53
|
|||
|
|||
Can I Use a Count Function for Text?
You may want to use pivot charts. First you create a colum (to count) put a
value of 1 in that colum for all the 1000. Next, with your curser within your worksheet, go to the Data and select the pivot tables. Follow the steps. It will ask you whether to create the pivot in the same sheet or different sheet. Choose different sheet. Next it will give you the option of organizing the data the way you want. In the body, where it says data, put the count variable which you generated. In the left hand colum put the job role. Pivot table works beautifully, I just finished working on something similar to yours. if this is not clear, go to the help menu and type in pivot tables, they explain very well. Hope it helps, Nasreen "Robert" wrote: Hi Bob, I am hoping you can help me. I have a list of about 1000 employees and column AC list their job role. i.e. Project Manager, Developer, etc. Is there a way to get a count of how many people are in each role? thanks, robert "Bob Phillips" wrote: I think so, it is a wildcard. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joan NYC" wrote in message ... Thanks Bob I will try it I guess the "*" is the answer! "Bob Phillips" wrote: =COUNTIF(A1:A100,"*@*") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joan NYC" wrote in message ... Aha... Sumproduct is not a function I ever tried I have a column of text. Some cells contain the character "@" with a space preceding the rest of text in the cell. I realize the @ could be considered an operator so it is preceded with an apostrophe to designate text. I want to sum the cells that have the "@" Thanks "David Biddulph" wrote: You'll need to be a bit clearer as to what you're trying to do. If COUNTIF or SUMIF doesn't do the job, you may want to try SUMPRODUCT. -- David Biddulph "Joan NYC" wrote in message ... I have been trying to calculate a column of Text in order to sum contents by certain criteria I have tried Count, CoutA and CountIF and cannot be able to get anything to work. Am I barking up the wrong tree? Can this be done? Thanks much. |
#54
|
|||
|
|||
Can I Use a Count Function for Text?
Hi I am trying to figure out how to count all the blocks that have text in
them. I have tried =COUNTIF(D2113,"*") but it counts only *, and it says in the help section that it should work. does any one have any Idea's? |
#55
|
|||
|
|||
Can I Use a Count Function for Text?
On Oct 30, 11:35*am, madchan001
wrote: Hi I am trying to figure out how to count all the blocks that have text in them. I have tried =COUNTIF(D2113,"*") but it counts only *, and it says in the help section that it should work. does any one have any Idea's? =SUMPRODUCT(--(ISTEXT(D2113))) |
#56
|
|||
|
|||
Can I Use a Count Function for Text?
I am trying to use a count funtion for text.
=COUNTIF(K225:X225,"LOA")--this one works equal 1 but I need it to equal 3 But I need it to include to more labels can you help me. =COUNTIF(K225:X225,"LOA,TRN,VAC") |
#57
|
|||
|
|||
Can I Use a Count Function for Text?
How about a nice macro
'=========== Option Compare Text Sub counttextinROW() mr = 2 fc = Range("K1").Column lc = Range("z1").Column For i = fc To lc If Cells(mr, i) = "loa" _ Or Cells(mr, i) = "b" _ Or Cells(mr, i) = "c" Then mCount = mCount + 1 End If Next i MsgBox mCount End Sub '============= -- Don Guillett Microsoft MVP Excel SalesAid Software "LaTanya" wrote in message news I am trying to use a count funtion for text. =COUNTIF(K225:X225,"LOA")--this one works equal 1 but I need it to equal 3 But I need it to include to more labels can you help me. =COUNTIF(K225:X225,"LOA,TRN,VAC") |
#58
|
|||
|
|||
Can I Use a Count Function for Text?
I work in a drawing office an di need to count drawings according to their
date issued and their revision, am i able to graphically show these resutls? use countif etc to make a table to produce a chart from... before i waste hours playing around i thought i would ask the qestion thank you Neil "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Steve Scatt" wrote in message ... Thanks Biff, tried it again and made sure no unseen characters and it seems to work. Cheers. "Steve Scatt" wrote: I have 2 separate columns and I am trying to count the nu,ber of occurances for a 2 different values. i.e. how many times admin column C and sick Column D show up. Tried the SUMPRODUCT and it doesn't seem to work. I get a value of 0 when there should be 3. "David Biddulph" wrote: =SUMPRODUCT((A1:A100="A")*(A1:A100="D")*(B1:B100 ="psychiatrist")) -- David Biddulph "Nick Brunetti" wrote in message ... I am trying to count a cell if criteria from two separate columns are met. For example, my first column has the letters A, B, C, D or no letters at all. The second column has different descriptive words (i.e. internal medicine, psychiatrist, etc). I would like a cell to be counted if a cell in the first column contains an A or B or C or D AND a cell in the second colum contains the word "psychiatrist". What is the best function/formula to use for this problem? "Bob Phillips" wrote: =COUNTIF(A1:A100,"*@*") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joan NYC" wrote in message ... Aha... Sumproduct is not a function I ever tried I have a column of text. Some cells contain the character "@" with a space preceding the rest of text in the cell. I realize the @ could be considered an operator so it is preceded with an apostrophe to designate text. I want to sum the cells that have the "@" Thanks "David Biddulph" wrote: You'll need to be a bit clearer as to what you're trying to do. If COUNTIF or SUMIF doesn't do the job, you may want to try SUMPRODUCT. -- David Biddulph "Joan NYC" wrote in message ... I have been trying to calculate a column of Text in order to sum contents by certain criteria I have tried Count, CoutA and CountIF and cannot be able to get anything to work. Am I barking up the wrong tree? Can this be done? Thanks much. |
#59
|
|||
|
|||
Can I Use a Count Function for Text?
I'm trying to do something similar. I need to count the number of apples in
column D. But i only need to count the ones in rows 25-35 and rows 50-75. I've tried countif, sumif, and sumproduct. Is there a way to do this? "Bob Phillips" wrote: =COUNTIF(A1:A100,"*@*") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joan NYC" wrote in message ... Aha... Sumproduct is not a function I ever tried I have a column of text. Some cells contain the character "@" with a space preceding the rest of text in the cell. I realize the @ could be considered an operator so it is preceded with an apostrophe to designate text. I want to sum the cells that have the "@" Thanks "David Biddulph" wrote: You'll need to be a bit clearer as to what you're trying to do. If COUNTIF or SUMIF doesn't do the job, you may want to try SUMPRODUCT. -- David Biddulph "Joan NYC" wrote in message ... I have been trying to calculate a column of Text in order to sum contents by certain criteria I have tried Count, CoutA and CountIF and cannot be able to get anything to work. Am I barking up the wrong tree? Can this be done? Thanks much. |
#60
|
|||
|
|||
Can I Use a Count Function for Text?
You could try this:
=COUNTIF(D2535,"apples")+COUNTIF(D5075,"apples ") Hope this helps. Pete On Jan 13, 12:27*am, jolineachi wrote: I'm trying to do something similar. *I need to count the number of apples in column D. *But i only need to count the ones in rows 25-35 and rows 50-75. * I've tried countif, sumif, and sumproduct. *Is there a way to do this? "Bob Phillips" wrote: =COUNTIF(A1:A100,"*@*") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joan NYC" wrote in message ... Aha... Sumproduct is not a function I ever tried I have a column of text. *Some cells contain the character "@" with a space preceding the rest of text in the cell. *I realize the @ could be considered an operator so it is preceded with an apostrophe to designate text. *I want to sum the cells that have the "@" Thanks "David Biddulph" wrote: You'll need to be a bit clearer as to what you're trying to do. *If COUNTIF or SUMIF doesn't do the job, you may want to try SUMPRODUCT. -- David Biddulph "Joan NYC" wrote in message ... I have been trying to calculate a column of Text in order to sum contents by certain criteria I have tried Count, CoutA and CountIF and cannot be able to get anything to work. Am I barking up the wrong tree? *Can this be done? Thanks much.- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|