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 |
#101
|
|||
|
|||
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. |
#102
|
|||
|
|||
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. |
#103
|
|||
|
|||
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. |
#104
|
|||
|
|||
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. |
#105
|
|||
|
|||
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. |
#106
|
|||
|
|||
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. |
#107
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|