Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells containing text
Hi, I am trying to find a formula that will give me a total of the cells from certain columns in each row which contain text, for example: I have text in some cells in columns a to f in row 1. I would like a total to appear in g1 telling me how many cells contain text from columns a, c & e excluding b, d & f. (The columns I want the totals from will be consistant for each row.) We have tried some formulas and cant get it to work. Don't know if we are using the wong ones or if we are putting them in incorrectly. Thanks :) Lisa -- Lisa W ------------------------------------------------------------------------ Lisa W's Profile: http://www.excelforum.com/member.php...o&userid=32497 View this thread: http://www.excelforum.com/showthread...hreadid=522894 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells containing text
What do the other cells contain? Numbers? Blank? HTH Otto
"Lisa W" wrote in message ... Hi, I am trying to find a formula that will give me a total of the cells from certain columns in each row which contain text, for example: I have text in some cells in columns a to f in row 1. I would like a total to appear in g1 telling me how many cells contain text from columns a, c & e excluding b, d & f. (The columns I want the totals from will be consistant for each row.) We have tried some formulas and cant get it to work. Don't know if we are using the wong ones or if we are putting them in incorrectly. Thanks :) Lisa -- Lisa W ------------------------------------------------------------------------ Lisa W's Profile: http://www.excelforum.com/member.php...o&userid=32497 View this thread: http://www.excelforum.com/showthread...hreadid=522894 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells containing text
Hopefully this is what you asked for?? For the actual worksheet I am working on: *columns a to k contain text and numbers or blanks - none of this data is wanted for totals *columns l,p,t,x,ab... contain text or are blank - these are the ones we want the totals at the end of each row ("L to O" is a set of data, so is "p to s" and so on. We want to know how many of these sets occur in each row - if the beginning column (l, p etc) contains text then the following 3 will too, otherwise blank Thanks! Lisa:) -- Lisa W ------------------------------------------------------------------------ Lisa W's Profile: http://www.excelforum.com/member.php...o&userid=32497 View this thread: http://www.excelforum.com/showthread...hreadid=522894 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells containing text
Hopefully this is what you asked for?? For the actual worksheet I am working on: *columns a to k contain text and numbers or blanks - none of this data is wanted for totals *columns l,p,t,x,ab... contain text or are blank - these are the ones we want the totals at the end of each row ("L to O" is a set of data, so is "p to s" and so on. We want to know how many of these sets occur in each row - if the beginning column (l, p etc) contains text then the following 3 will too, otherwise blank Thanks! Lisa:) Otto Moehrbach Wrote: What do the other cells contain? Numbers? Blank? HTH Otto "Lisa W" wrote in message ... Hi, I am trying to find a formula that will give me a total of the cells from certain columns in each row which contain text, for example: I have text in some cells in columns a to f in row 1. I would like a total to appear in g1 telling me how many cells contain text from columns a, c & e excluding b, d & f. (The columns I want the totals from will be consistant for each row.) We have tried some formulas and cant get it to work. Don't know if we are using the wong ones or if we are putting them in incorrectly. Thanks :) Lisa -- Lisa W ------------------------------------------------------------------------ Lisa W's Profile: http://www.excelforum.com/member.php...o&userid=32497 View this thread: http://www.excelforum.com/showthread...hreadid=522894 -- Lisa W ------------------------------------------------------------------------ Lisa W's Profile: http://www.excelforum.com/member.php...o&userid=32497 View this thread: http://www.excelforum.com/showthread...hreadid=522894 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells containing text
Lisa W wrote...
I am trying to find a formula that will give me a total of the cells from certain columns in each row which contain text, for example: I have text in some cells in columns a to f in row 1. I would like a total to appear in g1 telling me how many cells contain text from columns a, c & e excluding b, d & f. (The columns I want the totals from will be consistant for each row.) .... For relatively few nonadjacent columns, simplest to use something like =ISTEXT(A3)+ISTEXT(C3)+ISTEXT(E3) This would count cells evaluating as "" rather than blank as text. If you want to count only cells containing one or more characters of text, try =COUNTIF(A3,"?*")+COUNTIF(C3,"?*")+COUNTIF(E3,"?*" ) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells containing text
This may be helpfull if you can adapt it:
If you have any range (rows, columns, etc.) you can count text cells by using COUNTA() and COUNT(). For example if you want the count of text cells in column A from A1 to A500 use: =COUNTA(A1:A500)-COUNT(A1:A500) You can apply this to individual columns and then add the results -- Gary''s Student "Lisa W" wrote: Hopefully this is what you asked for?? For the actual worksheet I am working on: *columns a to k contain text and numbers or blanks - none of this data is wanted for totals *columns l,p,t,x,ab... contain text or are blank - these are the ones we want the totals at the end of each row ("L to O" is a set of data, so is "p to s" and so on. We want to know how many of these sets occur in each row - if the beginning column (l, p etc) contains text then the following 3 will too, otherwise blank Thanks! Lisa:) -- Lisa W ------------------------------------------------------------------------ Lisa W's Profile: http://www.excelforum.com/member.php...o&userid=32497 View this thread: http://www.excelforum.com/showthread...hreadid=522894 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
question about removing text from cells, leaving numbers | Excel Discussion (Misc queries) | |||
Linked cells and text boxes | Excel Discussion (Misc queries) | |||
Count Text Values in Column | Excel Discussion (Misc queries) | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |