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 |
#11
|
|||
|
|||
Can I Use a Count Function for Text?
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. |
#12
|
|||
|
|||
Can I Use a Count Function for Text?
Hi all,
I'm looking to do something along the same lines but using text and numbers. The formula below is what i've come up with =SUMPRODUCT((A2:A20="reason")*(D220,"0"")) I get a value of 0 or an error returning. I'm also looking to do a range for the numbers too, so B2 to B20 for numbers greater than 0 but less than 14 and equal to reason. Hope you can help. Many thanks Andy "Bob Phillips" wrote: =SUMPRODUCT((A2:A20={"A","B","C","D"})*(B2:B20="ps ychiatrist")) -- __________________________________ HTH Bob "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. |
#13
|
|||
|
|||
Can I Use a Count Function for Text?
I think you need this:
=SUMPRODUCT((A2:A20="reason")*(D220"")) The second term means cells in D220 are not blank. Your second question can be achieved like this: =SUMPRODUCT((A2:A20="reason")*(B2:B200)*(B2:B201 4)) Hope this helps. Pete On Jun 29, 1:41*pm, O2 andy O2 wrote: Hi all, I'm looking to do something along the same lines but using text and numbers. *The formula below is what i've come up with =SUMPRODUCT((A2:A20="reason")*(D220,"0"")) I get a value of 0 or an error returning. *I'm also looking to do a range for the numbers too, so B2 to B20 for numbers greater than 0 but less than 14 and equal to reason. Hope you can help. Many thanks Andy "Bob Phillips" wrote: =SUMPRODUCT((A2:A20={"A","B","C","D"})*(B2:B20="ps ychiatrist")) -- __________________________________ HTH Bob "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.- Hide quoted text - - Show quoted text - |
#14
|
|||
|
|||
Can I Use a Count Function for Text?
Great, thanks..
I'll give it a go and let you know how i get on.. "Pete_UK" wrote: I think you need this: =SUMPRODUCT((A2:A20="reason")*(D220"")) The second term means cells in D220 are not blank. Your second question can be achieved like this: =SUMPRODUCT((A2:A20="reason")*(B2:B200)*(B2:B201 4)) Hope this helps. Pete On Jun 29, 1:41 pm, O2 andy O2 wrote: Hi all, I'm looking to do something along the same lines but using text and numbers. The formula below is what i've come up with =SUMPRODUCT((A2:A20="reason")*(D220,"0"")) I get a value of 0 or an error returning. I'm also looking to do a range for the numbers too, so B2 to B20 for numbers greater than 0 but less than 14 and equal to reason. Hope you can help. Many thanks Andy "Bob Phillips" wrote: =SUMPRODUCT((A2:A20={"A","B","C","D"})*(B2:B20="ps ychiatrist")) -- __________________________________ HTH Bob "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.- Hide quoted text - - Show quoted text - |
#15
|
|||
|
|||
Can I Use a Count Function for Text?
Worked a treat Pete..
Thanks again..... "O2 andy" wrote: Great, thanks.. I'll give it a go and let you know how i get on.. "Pete_UK" wrote: I think you need this: =SUMPRODUCT((A2:A20="reason")*(D220"")) The second term means cells in D220 are not blank. Your second question can be achieved like this: =SUMPRODUCT((A2:A20="reason")*(B2:B200)*(B2:B201 4)) Hope this helps. Pete On Jun 29, 1:41 pm, O2 andy O2 wrote: Hi all, I'm looking to do something along the same lines but using text and numbers. The formula below is what i've come up with =SUMPRODUCT((A2:A20="reason")*(D220,"0"")) I get a value of 0 or an error returning. I'm also looking to do a range for the numbers too, so B2 to B20 for numbers greater than 0 but less than 14 and equal to reason. Hope you can help. Many thanks Andy "Bob Phillips" wrote: =SUMPRODUCT((A2:A20={"A","B","C","D"})*(B2:B20="ps ychiatrist")) -- __________________________________ HTH Bob "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.- Hide quoted text - - Show quoted text - |
#16
|
|||
|
|||
Can I Use a Count Function for Text?
You're welcome, Andy - thanks for feeding back.
Pete On Jun 29, 3:30*pm, O2 andy wrote: Worked a treat Pete.. Thanks again..... "O2 andy" wrote: Great, thanks.. I'll give it a go and let you know how i get on.. |
#17
|
|||
|
|||
I need to count a row with something in it
I have been following this strem but have not seen something that I need ....
I need to count a row (e.g., d61:j61) with anything in it - either a number or word .... can one of you please help me??? |
#18
|
|||
|
|||
I need to count a row with something in it
Maybe this:
=COUNTA(D61:J61) That will return the count of cells in the range that are not empty. -- Biff Microsoft Excel MVP "Rick" wrote in message ... I have been following this strem but have not seen something that I need .... I need to count a row (e.g., d61:j61) with anything in it - either a number or word .... can one of you please help me??? |
#19
|
|||
|
|||
I need to count a row with something in it
T. Valko..... You are the best..... this stuff is sooooo simple if you know
the basic rules ..... oh yea just like life Thanks again!!! "T. Valko" wrote: Maybe this: =COUNTA(D61:J61) That will return the count of cells in the range that are not empty. -- Biff Microsoft Excel MVP "Rick" wrote in message ... I have been following this strem but have not seen something that I need .... I need to count a row (e.g., d61:j61) with anything in it - either a number or word .... can one of you please help me??? |
#20
|
|||
|
|||
I need to count a row with something in it
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Rick" wrote in message ... T. Valko..... You are the best..... this stuff is sooooo simple if you know the basic rules ..... oh yea just like life Thanks again!!! "T. Valko" wrote: Maybe this: =COUNTA(D61:J61) That will return the count of cells in the range that are not empty. -- Biff Microsoft Excel MVP "Rick" wrote in message ... I have been following this strem but have not seen something that I need .... I need to count a row (e.g., d61:j61) with anything in it - either a number or word .... can one of you please help me??? |
Thread Tools | |
Display Modes | |
|
|