|
Can I Use a Count Function for Text?
I just found your discussion group. I also have two columns of information
and when I post the formula it simply appears as an entry. The cell where I posted the formula is a text cell. What should the cell format be? Sorry! This is all so new to me. -- ldmci "Steve Scatt" wrote: 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. |
Can I Use a Count Function for Text?
Usually format to General or Number.
THEN ... click back in the formula bar and hit <Enter to register the change. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ldmci" wrote in message ... I just found your discussion group. I also have two columns of information and when I post the formula it simply appears as an entry. The cell where I posted the formula is a text cell. What should the cell format be? Sorry! This is all so new to me. -- ldmci "Steve Scatt" wrote: 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. |
Can I Use a Count Function for Text?
eI am trying to put in the formula :
=COUNTIF(c4:c10, "cashiering") to count recurrently rows of information. The formula stays in the cell but the number does not materialize. I have tried reformatting the cells to reflect number or general to no avail. What am I doing wrong? Cathy M "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. |
Can I Use a Count Function for Text?
you saved my life, thanks for the post, my mgr will be pleased with the next
wave of spreadsheets "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. |
Can I Use a Count Function for Text?
Hello,
I also just found the discussion group and this is probably an easy fix, but everything I try give me an error. I have a column of RSVP's....so in each cell I have either a "yes" or a "no". I want a formula that will count the number of "yes"es so I know how many people are coming to an event. I don't want to have to count by hand, nor do I want to assign a value for each kind of response. I want Excel to simply count "yes". Make sense? kbrane "ldmci" wrote: I just found your discussion group. I also have two columns of information and when I post the formula it simply appears as an entry. The cell where I posted the formula is a text cell. What should the cell format be? Sorry! This is all so new to me. -- ldmci "Steve Scatt" wrote: 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. |
Can I Use a Count Function for Text?
=COUNTIF(A:A,"yes")
I wouldn't bother counting "no" because they aren't coming so you don't need a knife and fork for them. Gord Dibben MS Excel MVP On Tue, 26 Aug 2008 12:08:01 -0700, kbrane wrote: Hello, I also just found the discussion group and this is probably an easy fix, but everything I try give me an error. I have a column of RSVP's....so in each cell I have either a "yes" or a "no". I want a formula that will count the number of "yes"es so I know how many people are coming to an event. I don't want to have to count by hand, nor do I want to assign a value for each kind of response. I want Excel to simply count "yes". Make sense? kbrane "ldmci" wrote: I just found your discussion group. I also have two columns of information and when I post the formula it simply appears as an entry. The cell where I posted the formula is a text cell. What should the cell format be? Sorry! This is all so new to me. -- ldmci "Steve Scatt" wrote: 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. |
Can I Use a Count Function for Text?
I have a similar problem I want to see how many of each text item are in a
column. There only a dozen or so unique words out of a column of 450 rows but that text is not static so I cant just use =COUNTIF(c4:c10, "text") as the text value will change each month Is there a way to get use the countif formula to read each cell text entry and give me a total count of each text? Thanks |
Can I Use a Count Function for Text?
Just to clarify. If I have a column that looks like this:
|Biscuits | |Biscuits | |Biscuits | |Biscuits | |Potatoes | |Potatoes | |Potatoes | |Beef | |Beef | |Beef | |Beef | |Wine | I want a result like this: Wine = 1 Beef = 4 Potatoes = 3 Biscuits = 4 So that I dont have to type in Wine or beef in my formula. I need that variable to be calculated and entered automatically. I know I can do it in SQL but I dont want to have to create a database just for this.... Thanks "Chgrec" wrote: I have a similar problem I want to see how many of each text item are in a column. There only a dozen or so unique words out of a column of 450 rows but that text is not static so I cant just use =COUNTIF(c4:c10, "text") as the text value will change each month Is there a way to get use the countif formula to read each cell text entry and give me a total count of each text? Thanks |
Can I Use a Count Function for Text?
Use a PivotTable.
Chgrec wrote: Just to clarify. If I have a column that looks like this: |Biscuits | |Biscuits | |Biscuits | |Biscuits | |Potatoes | |Potatoes | |Potatoes | |Beef | |Beef | |Beef | |Beef | |Wine | I want a result like this: Wine = 1 Beef = 4 Potatoes = 3 Biscuits = 4 So that I dont have to type in Wine or beef in my formula. I need that variable to be calculated and entered automatically. I know I can do it in SQL but I dont want to have to create a database just for this.... Thanks "Chgrec" wrote: I have a similar problem I want to see how many of each text item are in a column. There only a dozen or so unique words out of a column of 450 rows but that text is not static so I cant just use =COUNTIF(c4:c10, "text") as the text value will change each month Is there a way to get use the countif formula to read each cell text entry and give me a total count of each text? Thanks |
Can I Use a Count Function for Text?
That is exactly what I neded, Thanks!!
Chris "Glenn" wrote: Use a PivotTable. Chgrec wrote: Just to clarify. If I have a column that looks like this: |Biscuits | |Biscuits | |Biscuits | |Biscuits | |Potatoes | |Potatoes | |Potatoes | |Beef | |Beef | |Beef | |Beef | |Wine | I want a result like this: Wine = 1 Beef = 4 Potatoes = 3 Biscuits = 4 So that I dont have to type in Wine or beef in my formula. I need that variable to be calculated and entered automatically. I know I can do it in SQL but I dont want to have to create a database just for this.... Thanks "Chgrec" wrote: I have a similar problem I want to see how many of each text item are in a column. There only a dozen or so unique words out of a column of 450 rows but that text is not static so I cant just use =COUNTIF(c4:c10, "text") as the text value will change each month Is there a way to get use the countif formula to read each cell text entry and give me a total count of each text? Thanks |
Can I Use a Count Function for Text?
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. |
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 |
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. |
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(D2:D113,"*") but it counts only *, and it says in the help section that it should work. does any one have any Idea's? |
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(D2:D113,"*") but it counts only *, and it says in the help section that it should work. does any one have any Idea's? =SUMPRODUCT(--(ISTEXT(D2:D113))) |
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") |
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 ... 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") |
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. |
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. |
Can I Use a Count Function for Text?
You could try this:
=COUNTIF(D25:D35,"apples")+COUNTIF(D50:D75,"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 - |
Can I Use a Count Function for Text?
Thanks Pete! You're a life saver!!!
"Pete_UK" wrote: You could try this: =COUNTIF(D25:D35,"apples")+COUNTIF(D50:D75,"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 - |
Can I Use a Count Function for Text?
You're welcome - thanks for feeding back.
Pete On Jan 13, 2:15*am, jolineachi wrote: Thanks Pete! *You're a life saver!!! |
Can I Use a Count Function for Text?
Hi,
I need some big help pls. Im working on a sheet and Its giving me sho much error. 1. I want to be able to have a automatic respoance come up in another cell depending on the answer giving in one from a drop down list. E.g. If "Service Department" is selected in C2 then "John Doe" would come up in E2. What I want is no matter what Department I select it would give me the correct manger instead of me having to type in the names or look in list to try match name. List would be pre done 2. Im also trying to have a count done based on if Yes, No or N/A is input from drop down list. Rows 1-200 has information but I need to have a count to be done automatically and transfered to another sheet in same workbook with the total amount of Yes, No or N/A. 3. Finally, say I select Service Deparment (which has 10 different job position) from drop down list in C2, to have correct list of positions appear in drop down list for D2 and depending on which position is selected in D2 correct list of Employees is in drop down list in E2. thanks alot if anyone can help me. It would really ease my pain. "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. |
Can I Use a Count Function for Text?
1. Use a VLOOKUP formula with a two column lookup table on another sheet.
2. =COUNTIF(Sheet1!A1:A200,"Yes") or "No" or "#N/A" 3. See Debra Dalgleish's site for dependent dropdowns. http://www.contextures.on.ca/xlDataVal02.html Gord Dibben MS Excel MVP On Mon, 19 Jan 2009 13:06:04 -0800, djs wrote: Hi, I need some big help pls. Im working on a sheet and Its giving me sho much error. 1. I want to be able to have a automatic respoance come up in another cell depending on the answer giving in one from a drop down list. E.g. If "Service Department" is selected in C2 then "John Doe" would come up in E2. What I want is no matter what Department I select it would give me the correct manger instead of me having to type in the names or look in list to try match name. List would be pre done 2. Im also trying to have a count done based on if Yes, No or N/A is input from drop down list. Rows 1-200 has information but I need to have a count to be done automatically and transfered to another sheet in same workbook with the total amount of Yes, No or N/A. 3. Finally, say I select Service Deparment (which has 10 different job position) from drop down list in C2, to have correct list of positions appear in drop down list for D2 and depending on which position is selected in D2 correct list of Employees is in drop down list in E2. thanks alot if anyone can help me. It would really ease my pain. "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. |
Can I Use a Count Function for Text?
I have another question:
I need to find the number of cases that were "Dismissed" in Column C between "01/01/08" and "12/31/08" in Column D. The formula I tried was =SUMPRODUCT(D149:D160="Guilty")*(E149:E160="01/01/07")*(E149:E160<="12/31/07") I keep getting 0 when I should get 3. Can anyone help me? "jolineachi" wrote: Thanks Pete! You're a life saver!!! "Pete_UK" wrote: You could try this: =COUNTIF(D25:D35,"apples")+COUNTIF(D50:D75,"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 - |
Can I Use a Count Function for Text?
The formula I tried was
=SUMPRODUCT(D149:D160="Guilty")*(E149:E160="01/01/07")*(E149:E160<="12/31/07") That doesn't match your description at all! Try it like this: =SUMPRODUCT(--(D149:D160="Dismissed"),--(E149:E160=DATE(2008,1,1)),--(E149:E160<=DATE(2008,12,31))) Better to use cells to hold the criteria: A1 = Dismissed B1 = start date C1 = end date =SUMPRODUCT(--(D149:D160=A1),--(E149:E160=B1),--(E149:E160<=C1)) Or, if your time period is for the entire specific year: =SUMPRODUCT(--(D149:D160=A1),--(YEAR(E149:E160)=2008)) -- Biff Microsoft Excel MVP "jolineachi" wrote in message ... I have another question: I need to find the number of cases that were "Dismissed" in Column C between "01/01/08" and "12/31/08" in Column D. The formula I tried was =SUMPRODUCT(D149:D160="Guilty")*(E149:E160="01/01/07")*(E149:E160<="12/31/07") I keep getting 0 when I should get 3. Can anyone help me? "jolineachi" wrote: Thanks Pete! You're a life saver!!! "Pete_UK" wrote: You could try this: =COUNTIF(D25:D35,"apples")+COUNTIF(D50:D75,"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 - |
Can I Use a Count Function for Text?
I have another question:
I need to find the number of cases that were "Dismissed" in Column C between "01/01/08" and "12/31/08" in Column D. The formula I tried was =SUMPRODUCT(D149:D160="Guilty")*(E149:E160="01/01/07")*(E149:E160<="12/31/07") I keep getting 0 when I should get 3. Can anyone help me? "Pete_UK" wrote: You're welcome - thanks for feeding back. Pete On Jan 13, 2:15 am, jolineachi wrote: Thanks Pete! You're a life saver!!! |
Can I Use a Count Function for Text?
Sorry! The formula is supposed to read "Dismissed". Does anyone know how to
delete a post? |
Can I Use a Count Function for Text?
Thanks T.Valko! The first formula is great for when I compile my semi-annual
reports and the third formula is perfect for my annual reports. Thanks a bunch!!! |
Can I Use a Count Function for Text?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "jolineachi" wrote in message ... Thanks T.Valko! The first formula is great for when I compile my semi-annual reports and the third formula is perfect for my annual reports. Thanks a bunch!!! |
Can I Use a Count Function for Text?
I have a similar problem but your suggestion isn't working for me.
I have two columns of data that I want to count, but I only want to count the intersection of the two columns. So, if A1 and B1 both contain the same text I only want to count it once. Is that possible to do? "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. |
Can I Use a Count Function for Text?
Maybe this:
=SUMPRODUCT(--(A1:A5=B1:B5)) ...........A..........B 1........x...........x 2........y...........z 3........c...........c 4........s...........s 5........v..........w Based on that sample data the result would be 3. -- Biff Microsoft Excel MVP "Nichole Beck" <Nichole wrote in message ... I have a similar problem but your suggestion isn't working for me. I have two columns of data that I want to count, but I only want to count the intersection of the two columns. So, if A1 and B1 both contain the same text I only want to count it once. Is that possible to do? "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. |
Can I Use a Count Function for Text?
"Joan NYC" wrote: 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. |
Can I Use a Count Function for Text?
"Joan NYC" wrote: 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. |
Can I Use a Count Function for Text?
Need more information. Can you provide an exampleo of what you're trying to do? -- Christopher770 ------------------------------------------------------------------------ Christopher770's Profile: http://www.thecodecage.com/forumz/member.php?userid=188 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=74543 |
Can I Use a Count Function for Text?
When you say calculate, do you mean SUM rather than COUNT?gaelf;267108 Wrote: "Joan NYC" wrote: 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. -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=74543 |
Can I Use a Count Function for Text?
Here is my question:
How can I use a formula to create the following: I want one column to be three-five different text (i.e. W, L, D, etc..) and the next column to convert that text into a number (10, 5, 0, etc..) I have been trying to figure this out all day, and any help is greatly appreciated. Thank you, |
Can I Use a Count Function for Text?
=LOOKUP(A1,{"D","L","W"},{0,5,10}) entered in B1
Note the lookup_vector {"D","L","W"} must be in ascending order. Gord Dibben MS Excel MVP On Wed, 22 Apr 2009 13:29:02 -0700, Davina wrote: Here is my question: How can I use a formula to create the following: I want one column to be three-five different text (i.e. W, L, D, etc..) and the next column to convert that text into a number (10, 5, 0, etc..) I have been trying to figure this out all day, and any help is greatly appreciated. Thank you, |
Can I Use a Count Function for Text?
I found these comments very helpful so far. Now I am stumped.
I have a workbook with multiple worksheets and I am making a summary page up front for management review. Here's what I am trying to do. From worksheet named Q2, I have all my projects leads in column D, and then the current status of their projects in column E, for instance, intake, editing, pending, etc.. What I am trying to do is count all the instances of one project lead, in this case chris craig, and then I want to know all projects she is working on, unless, or except, if the status is completed or carried forward. If the status for a project lead is completed or carried, then do not count. This is the formula I am trying to use, but is is counting all projects for the given lead with all statuses, including completed and carried forward. =SUMPRODUCT(('Q2'!D8:D33="chris craig")*('Q2'!E8:E33<"completed, carried forward")) "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. |
| All times are GMT +1. The time now is 07:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com