|
Can I Use a Count Function for Text?
With the helper column I copied down, it did not return as it should:
Whether I used =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. or =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. For column E on my Q2 worksheet I am using a Validation, List, from the bottom of my sheet, so the project lead can have a drop-down box to select their current status. So there would not be any abnormal characters or spaces when I am trying to comprise my formula. "David Biddulph" wrote: That probably means that you've got some slight difference in the content of column E, perhaps spare spaces or other characters (such as non-breaking spaces) in the cell. You can check with a helper column: =('Q2'!E8<"completed, carried forward") and copy down and/or =('Q2'!E8="completed, carried forward") and copy down. You might also want to try =LEN('Q2'!E8)=26 You might wish to copy the string (which you think is ="completed, carried forward") from a suitable cell in column E into your formula. -- David Biddulph "Richard Horn" <Richard wrote in message ... 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. |
Can I Use a Count Function for Text?
For instance:
Project Lead Status Date Chris Craig Client Testing 05/15/09 Chris Craig Completed 04/17/09 What I want to return is "1". In other words, all projects that "chris craig" is working on, that the project status is not "completed" or the status is not "carried forward". hope this helps. "Richard Horn" wrote: With the helper column I copied down, it did not return as it should: Whether I used =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. or =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. For column E on my Q2 worksheet I am using a Validation, List, from the bottom of my sheet, so the project lead can have a drop-down box to select their current status. So there would not be any abnormal characters or spaces when I am trying to comprise my formula. "David Biddulph" wrote: That probably means that you've got some slight difference in the content of column E, perhaps spare spaces or other characters (such as non-breaking spaces) in the cell. You can check with a helper column: =('Q2'!E8<"completed, carried forward") and copy down and/or =('Q2'!E8="completed, carried forward") and copy down. You might also want to try =LEN('Q2'!E8)=26 You might wish to copy the string (which you think is ="completed, carried forward") from a suitable cell in column E into your formula. -- David Biddulph "Richard Horn" <Richard wrote in message ... 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. |
Can I Use a Count Function for Text?
So you've confirmed that you have a problem, and that your formula doesn't
match your data, but you've not bothered with my other suggestions? -- David Biddulph Richard Horn wrote: With the helper column I copied down, it did not return as it should: Whether I used =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. or =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. For column E on my Q2 worksheet I am using a Validation, List, from the bottom of my sheet, so the project lead can have a drop-down box to select their current status. So there would not be any abnormal characters or spaces when I am trying to comprise my formula. "David Biddulph" wrote: That probably means that you've got some slight difference in the content of column E, perhaps spare spaces or other characters (such as non-breaking spaces) in the cell. You can check with a helper column: =('Q2'!E8<"completed, carried forward") and copy down and/or =('Q2'!E8="completed, carried forward") and copy down. You might also want to try =LEN('Q2'!E8)=26 You might wish to copy the string (which you think is ="completed, carried forward") from a suitable cell in column E into your formula. -- David Biddulph "Richard Horn" <Richard wrote in message ... 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. |
Can I Use a Count Function for Text?
Yes David, that's affirmative. I am certainily stuck.
I tried your 3 suggestions: 1. =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. 2. =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. 3. =LEN('Q2'!E8)=26 which returned all FALSE even though column does contain a Completed status. Each of your suggested helpers I pasted in a cell, then copied them down to cover a range of cells I wanted to test query. Not unless I am totally misunderstanding what your trying to help me with, I am not sure how to make this work, but I certainly appreciate your patience is trying to share your experience. thanks Richard "David Biddulph" wrote: So you've confirmed that you have a problem, and that your formula doesn't match your data, but you've not bothered with my other suggestions? -- David Biddulph Richard Horn wrote: With the helper column I copied down, it did not return as it should: Whether I used =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. or =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. For column E on my Q2 worksheet I am using a Validation, List, from the bottom of my sheet, so the project lead can have a drop-down box to select their current status. So there would not be any abnormal characters or spaces when I am trying to comprise my formula. "David Biddulph" wrote: That probably means that you've got some slight difference in the content of column E, perhaps spare spaces or other characters (such as non-breaking spaces) in the cell. You can check with a helper column: =('Q2'!E8<"completed, carried forward") and copy down and/or =('Q2'!E8="completed, carried forward") and copy down. You might also want to try =LEN('Q2'!E8)=26 You might wish to copy the string (which you think is ="completed, carried forward") from a suitable cell in column E into your formula. -- David Biddulph "Richard Horn" <Richard wrote in message ... 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. |
Can I Use a Count Function for Text?
As =LEN('Q2'!E8)=26 returned FALSE, that confirms (as I suggested earlier)
that the content of your cell is not "completed, carried forward", because if you copy the string "completed, carried forward" [without the quotes] into the cell and use the LEN function on that you will see that it IS 26 characters. What does =LEN('Q2'!E8) return? How many characters are in the cell, if it isn't 26? What does =CODE(RIGHT(A8)) return? It will be 100 if the last character in the cell is "d". It will be 32 if the last character is a space, and 160 if it is a non-breaking space. -- David Biddulph Richard Horn wrote: Yes David, that's affirmative. I am certainily stuck. I tried your 3 suggestions: 1. =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. 2. =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. 3. =LEN('Q2'!E8)=26 which returned all FALSE even though column does contain a Completed status. Each of your suggested helpers I pasted in a cell, then copied them down to cover a range of cells I wanted to test query. Not unless I am totally misunderstanding what your trying to help me with, I am not sure how to make this work, but I certainly appreciate your patience is trying to share your experience. thanks Richard "David Biddulph" wrote: So you've confirmed that you have a problem, and that your formula doesn't match your data, but you've not bothered with my other suggestions? -- David Biddulph Richard Horn wrote: With the helper column I copied down, it did not return as it should: Whether I used =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. or =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. For column E on my Q2 worksheet I am using a Validation, List, from the bottom of my sheet, so the project lead can have a drop-down box to select their current status. So there would not be any abnormal characters or spaces when I am trying to comprise my formula. "David Biddulph" wrote: That probably means that you've got some slight difference in the content of column E, perhaps spare spaces or other characters (such as non-breaking spaces) in the cell. You can check with a helper column: =('Q2'!E8<"completed, carried forward") and copy down and/or =('Q2'!E8="completed, carried forward") and copy down. You might also want to try =LEN('Q2'!E8)=26 You might wish to copy the string (which you think is ="completed, carried forward") from a suitable cell in column E into your formula. -- David Biddulph "Richard Horn" <Richard wrote in message ... 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. |
Can I Use a Count Function for Text?
Oh my gosh! David I am so sorry.
The cell I am quering does not contain "completed, carried forward" The cell would contain either "completed" or "carried forward". It would be one or the otther. I wanted to capture who the project lead is in column D, "chris craig" and any project she is working on, unless the status is "completed" or "carried forward" in column E. If the column E entry is "completed" or "carried forward" I do not want that in the count. Again, I apologize for not being so precise, as I know when making a formula in Excel, you must be spot on. richard "David Biddulph" wrote: As =LEN('Q2'!E8)=26 returned FALSE, that confirms (as I suggested earlier) that the content of your cell is not "completed, carried forward", because if you copy the string "completed, carried forward" [without the quotes] into the cell and use the LEN function on that you will see that it IS 26 characters. What does =LEN('Q2'!E8) return? How many characters are in the cell, if it isn't 26? What does =CODE(RIGHT(A8)) return? It will be 100 if the last character in the cell is "d". It will be 32 if the last character is a space, and 160 if it is a non-breaking space. -- David Biddulph Richard Horn wrote: Yes David, that's affirmative. I am certainily stuck. I tried your 3 suggestions: 1. =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. 2. =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. 3. =LEN('Q2'!E8)=26 which returned all FALSE even though column does contain a Completed status. Each of your suggested helpers I pasted in a cell, then copied them down to cover a range of cells I wanted to test query. Not unless I am totally misunderstanding what your trying to help me with, I am not sure how to make this work, but I certainly appreciate your patience is trying to share your experience. thanks Richard "David Biddulph" wrote: So you've confirmed that you have a problem, and that your formula doesn't match your data, but you've not bothered with my other suggestions? -- David Biddulph Richard Horn wrote: With the helper column I copied down, it did not return as it should: Whether I used =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. or =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. For column E on my Q2 worksheet I am using a Validation, List, from the bottom of my sheet, so the project lead can have a drop-down box to select their current status. So there would not be any abnormal characters or spaces when I am trying to comprise my formula. "David Biddulph" wrote: That probably means that you've got some slight difference in the content of column E, perhaps spare spaces or other characters (such as non-breaking spaces) in the cell. You can check with a helper column: =('Q2'!E8<"completed, carried forward") and copy down and/or =('Q2'!E8="completed, carried forward") and copy down. You might also want to try =LEN('Q2'!E8)=26 You might wish to copy the string (which you think is ="completed, carried forward") from a suitable cell in column E into your formula. -- David Biddulph "Richard Horn" <Richard wrote in message ... 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. |
Can I Use a Count Function for Text?
OK, now that you realise that to get the right answer you must ask the right
question, let's go back a few days. Try changing =SUMPRODUCT(('Q2'!D8:D33="chris craig")*('Q2'!E8:E33<"completed, carried forward")) to =SUMPRODUCT(('Q2'!D8:D33="chris craig")*('Q2'!E8:E33<"completed")*('Q2'!E8:E33<" carried forward")) -- David Biddulph Richard Horn wrote: Oh my gosh! David I am so sorry. The cell I am quering does not contain "completed, carried forward" The cell would contain either "completed" or "carried forward". It would be one or the otther. I wanted to capture who the project lead is in column D, "chris craig" and any project she is working on, unless the status is "completed" or "carried forward" in column E. If the column E entry is "completed" or "carried forward" I do not want that in the count. Again, I apologize for not being so precise, as I know when making a formula in Excel, you must be spot on. richard "David Biddulph" wrote: As =LEN('Q2'!E8)=26 returned FALSE, that confirms (as I suggested earlier) that the content of your cell is not "completed, carried forward", because if you copy the string "completed, carried forward" [without the quotes] into the cell and use the LEN function on that you will see that it IS 26 characters. What does =LEN('Q2'!E8) return? How many characters are in the cell, if it isn't 26? What does =CODE(RIGHT(A8)) return? It will be 100 if the last character in the cell is "d". It will be 32 if the last character is a space, and 160 if it is a non-breaking space. -- David Biddulph Richard Horn wrote: Yes David, that's affirmative. I am certainily stuck. I tried your 3 suggestions: 1. =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. 2. =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. 3. =LEN('Q2'!E8)=26 which returned all FALSE even though column does contain a Completed status. Each of your suggested helpers I pasted in a cell, then copied them down to cover a range of cells I wanted to test query. Not unless I am totally misunderstanding what your trying to help me with, I am not sure how to make this work, but I certainly appreciate your patience is trying to share your experience. thanks Richard "David Biddulph" wrote: So you've confirmed that you have a problem, and that your formula doesn't match your data, but you've not bothered with my other suggestions? -- David Biddulph Richard Horn wrote: With the helper column I copied down, it did not return as it should: Whether I used =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. or =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. For column E on my Q2 worksheet I am using a Validation, List, from the bottom of my sheet, so the project lead can have a drop-down box to select their current status. So there would not be any abnormal characters or spaces when I am trying to comprise my formula. "David Biddulph" wrote: That probably means that you've got some slight difference in the content of column E, perhaps spare spaces or other characters (such as non-breaking spaces) in the cell. You can check with a helper column: =('Q2'!E8<"completed, carried forward") and copy down and/or =('Q2'!E8="completed, carried forward") and copy down. You might also want to try =LEN('Q2'!E8)=26 You might wish to copy the string (which you think is ="completed, carried forward") from a suitable cell in column E into your formula. -- David Biddulph "Richard Horn" <Richard wrote in message ... 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. |
Can I Use a Count Function for Text?
Bingo! David, you are a genius.
thanks so much for your expertise and patience. Richard "David Biddulph" wrote: OK, now that you realise that to get the right answer you must ask the right question, let's go back a few days. Try changing =SUMPRODUCT(('Q2'!D8:D33="chris craig")*('Q2'!E8:E33<"completed, carried forward")) to =SUMPRODUCT(('Q2'!D8:D33="chris craig")*('Q2'!E8:E33<"completed")*('Q2'!E8:E33<" carried forward")) -- David Biddulph Richard Horn wrote: Oh my gosh! David I am so sorry. The cell I am quering does not contain "completed, carried forward" The cell would contain either "completed" or "carried forward". It would be one or the otther. I wanted to capture who the project lead is in column D, "chris craig" and any project she is working on, unless the status is "completed" or "carried forward" in column E. If the column E entry is "completed" or "carried forward" I do not want that in the count. Again, I apologize for not being so precise, as I know when making a formula in Excel, you must be spot on. richard "David Biddulph" wrote: As =LEN('Q2'!E8)=26 returned FALSE, that confirms (as I suggested earlier) that the content of your cell is not "completed, carried forward", because if you copy the string "completed, carried forward" [without the quotes] into the cell and use the LEN function on that you will see that it IS 26 characters. What does =LEN('Q2'!E8) return? How many characters are in the cell, if it isn't 26? What does =CODE(RIGHT(A8)) return? It will be 100 if the last character in the cell is "d". It will be 32 if the last character is a space, and 160 if it is a non-breaking space. -- David Biddulph Richard Horn wrote: Yes David, that's affirmative. I am certainily stuck. I tried your 3 suggestions: 1. =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. 2. =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. 3. =LEN('Q2'!E8)=26 which returned all FALSE even though column does contain a Completed status. Each of your suggested helpers I pasted in a cell, then copied them down to cover a range of cells I wanted to test query. Not unless I am totally misunderstanding what your trying to help me with, I am not sure how to make this work, but I certainly appreciate your patience is trying to share your experience. thanks Richard "David Biddulph" wrote: So you've confirmed that you have a problem, and that your formula doesn't match your data, but you've not bothered with my other suggestions? -- David Biddulph Richard Horn wrote: With the helper column I copied down, it did not return as it should: Whether I used =('Q2'!E8<"completed, carried forward") which returned all TRUE even though column does contain a Completed status. or =('Q2'!E8="completed, carried forward") which returned all FALSE even though column does contain a Completed status. For column E on my Q2 worksheet I am using a Validation, List, from the bottom of my sheet, so the project lead can have a drop-down box to select their current status. So there would not be any abnormal characters or spaces when I am trying to comprise my formula. "David Biddulph" wrote: That probably means that you've got some slight difference in the content of column E, perhaps spare spaces or other characters (such as non-breaking spaces) in the cell. You can check with a helper column: =('Q2'!E8<"completed, carried forward") and copy down and/or =('Q2'!E8="completed, carried forward") and copy down. You might also want to try =LEN('Q2'!E8)=26 You might wish to copy the string (which you think is ="completed, carried forward") from a suitable cell in column E into your formula. -- David Biddulph "Richard Horn" <Richard wrote in message ... 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. |
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?
I need to count the number of text characters per cell to ensure we don't go
over 34 characters a line for major print jobs. Each line will be in a different cell. How can I do this? "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?
Characters per cell =LEN(cellref)
All that does is tell you how many chars per cell including spaces. Do you want to automatically limit the numbers of chars to a maximum of 34? You could set up event code to truncate anything over a certain number of characters after user hits ENTER key Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A1:A10" 'edit to suit ' "A1,A2,B1,C5,C6" for a non-contiguous range example Dim cell As Range On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If Len(.Value) 34 Then .Value = Left(.Value, 34) End If End With End If ws_exit: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste into that module, Edit the range to suit. Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Mon, 15 Jun 2009 08:24:05 -0700, LadyJags wrote: I need to count the number of text characters per cell to ensure we don't go over 34 characters a line for major print jobs. Each line will be in a different cell. How can I do this? "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,
Im hoping that someone can help me.. I am trying to count how many export has been done for each Region for July, how many for August and so on. Ill try to make myself a bit clear. For example: My first column (A1:A100) contains 10 different Regions (Netherlands, Italy, Spain etc). The second column (B1:B100) contains months ( July, August, September, October etc). I would like to know if there is formula to count how many times Netherlands July appears in those 2 columns. In both columns data must be chosen from a drop down menu.. Hope I made myself clear.. Can this be done ? Thanks Antonella "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?
Hi,
Im hoping that someone can help me.. I am trying to count how many export has been done for each Region for July, how many for August and so on. Ill try to make myself a bit clear. For example: My first column (A1:A100) contains 10 different Regions (Netherlands, Italy, Spain etc). The second column (B1:B100) contains months ( July, August, September, October etc). I would like to know if there is formula to count how many times Netherlands July appears in those 2 columns. In both columns data must be chosen from a drop down menu.. Hope I made myself clear.. Can this be done ? Thanks Antonella "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,
Im hoping that you can help me.. I am trying to count how many export has been done for each Region for July, how many for August and so on. Ill try to make myself a bit clear. For example: My first column (A1:A100) contains 10 different Regions (Netherlands, Italy, Spain etc). The second column (B1:B100) contains months ( July, August, September, October etc). I would like to know if there is formula to count how many times Netherlands July appears in those 2 columns. Hope I made myself clear.. Can this be done ? I've tried COUNTIF but did not work. How can I nest COUNTIF and AND function? Thanks "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?
See the attached workbook. There may be an easier way, but by using the DCOUNTA function, you can define a critera range that can handle multiple criteria. Hope this helps! :Bgr +-------------------------------------------------------------------+ |Filename: Book2.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=181| +-------------------------------------------------------------------+ -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117385 |
Can I Use a Count Function for Text?
Sumproduct is the function that you'll want to use.
However, the proper configuration of the arguments in that function will depend on *exactly* how the months are being entered into Column B. Are the names of the months TEXT entries, OR, are they XL recognized dates, formatted to display just the month name? For months entered as text in Column B: In C1 type in the name of the Region, and in C2 type in the name of the month, then try this: =Sumproduct((A1:A100=C1)*(B1:B100=C2)) For months entered as "legal" XL dates in Column B, try this: =SUMPRODUCT((A1:A100=C1)*(TEXT(B1:B100,"mmmm")=C2) ) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Antonella" wrote in message ... Hi, Im hoping that someone can help me.. I am trying to count how many export has been done for each Region for July, how many for August and so on. Ill try to make myself a bit clear. For example: My first column (A1:A100) contains 10 different Regions (Netherlands, Italy, Spain etc). The second column (B1:B100) contains months ( July, August, September, October etc). I would like to know if there is formula to count how many times Netherlands July appears in those 2 columns. In both columns data must be chosen from a drop down menu.. Hope I made myself clear.. Can this be done ? Thanks Antonella "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?
A workbook illustrating a solution to your requirements was posted as a reply to one of your previous posts with the same subject line. Please check it out and, if it doesn't fit your needs, expand your definition of the problem.... -- jamescox ------------------------------------------------------------------------ jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117382 |
Can I Use a Count Function for Text?
On Jul 19, 3:43 pm, jamescox wrote:
A workbook illustrating a solution to your requirements was posted as a reply to one of your previous posts with the same subject line. Please check it out and, if it doesn't fit your needs, expand your definition of the problem.... -- jamescox ------------------------------------------------------------------------ jamescox's Profile:http://www.thecodecage.com/forumz/member.php?userid=449 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=117382 Hi, for an alternative to SUMPRODUCT have a look at http://sulprobil.com/html/listfreq.html, maybe you find help there. Have fun, cheers Michael |
Can I Use a Count Function for Text?
I agree!!! Bob...you are a WONDERFUL man! I too could not figure out why it
was not working until I added the *'s in. Worked like a charm! :) God bless! "catwoman48" wrote: "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. <<SMAK You wonderful person! I just spent an hour and a half doing research on this very thing. Couldn't figure out why my formula(s) wouldn't work. I had "wildcard" in the back of my mind, but that seemed too simple, and didn't make sense, as my search criterion was not a symbol. But it worked, and I thank you a million times. |
Can I Use a Count Function for Text?
David
I have a similar problem where I am tring to count the occurance of a text in one column and a number value in a second column. For instance conlum A contains 'Y' or 'N' and column C contians numbers 1, 2, 3, etc. So I want to count the number of occurances where column A is 'Y' and column C is '3'. Can you help with this? Thanks conner34 "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?
=sumproduct(--(a1:a1000="Y"),--(c1:c1000=3))
Adjust the ranges to suit. Regards, Fred "conner34" wrote in message ... David I have a similar problem where I am tring to count the occurance of a text in one column and a number value in a second column. For instance conlum A contains 'Y' or 'N' and column C contians numbers 1, 2, 3, etc. So I want to count the number of occurances where column A is 'Y' and column C is '3'. Can you help with this? Thanks conner34 "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?
"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'm using the Count If formula you mentioned below...My question is - if
you are summing text and you want the formula to be maintained if you add columns in later, how do you do that? For example, if I am counting the number of x's in columns A1:A10 and I add a column at A5, I want to be sure it automatically includes those in the sum and that the formula now covers A1:A11.... "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?
The formula should automatically adjust for the inserted ROW.
=COUNTIF(A1:A10,"x") If I insert a new ROW 5 the formula automatically adjusts to: =COUNTIF(A1:A11,"x") -- Biff Microsoft Excel MVP "Debbie Amateur" wrote in message ... Hi - I'm using the Count If formula you mentioned below...My question is - if you are summing text and you want the formula to be maintained if you add columns in later, how do you do that? For example, if I am counting the number of x's in columns A1:A10 and I add a column at A5, I want to be sure it automatically includes those in the sum and that the formula now covers A1:A11.... "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 just tried this and it worked perfectly! Thank you!
"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 instance that I need help with.
I have a string of letters in 1 cell and I want to count how many times a letter occurs in that cell and eventually count all occurances of all letters in that one cell. Any ideas? As an example: A1 contains "catgctagccatgca" as text. I want to know many times a, c, g, and t occurs in that string. "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 Bob,
The below function helped me start out. But I need to add the letters in each column and give them different values and then subsequently add them for a total of nurses scheduled. How would I go about creating that function? Thank you so much for any help you can give. "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:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com