| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
counting text cells
Hi, I need to count the number of cells in a range that have text in the
cell. There are a number of blank cells that when I use the counta function are included in the count. I don't want the blank cells counted. Any suggestions? Ex: A1 contains 'oranges';A2 to A4 is blank; A5 contains 'apples'; A6 contains 'oranges' A7 contains 'pears' I want the total in cell A8 to be 4. |
|
#2
|
|||
|
|||
|
=SUMPRODUCT(--(LEN(A1:A7)0))
HTH Jason Atlanta, GA -----Original Message----- Hi, I need to count the number of cells in a range that have text in the cell. There are a number of blank cells that when I use the counta function are included in the count. I don't want the blank cells counted. Any suggestions? Ex: A1 contains 'oranges';A2 to A4 is blank; A5 contains 'apples'; A6 contains 'oranges' A7 contains 'pears' I want the total in cell A8 to be 4. . |
|
#3
|
|||
|
|||
|
Thanks Jason, but this is still returning 7, what am I doing wrong??
"Jason Morin" wrote: =SUMPRODUCT(--(LEN(A1:A7)0)) HTH Jason Atlanta, GA -----Original Message----- Hi, I need to count the number of cells in a range that have text in the cell. There are a number of blank cells that when I use the counta function are included in the count. I don't want the blank cells counted. Any suggestions? Ex: A1 contains 'oranges';A2 to A4 is blank; A5 contains 'apples'; A6 contains 'oranges' A7 contains 'pears' I want the total in cell A8 to be 4. . |
|
#4
|
|||
|
|||
|
=COUNTIF(A1:A7,"?*")
which excludes zero-length text values. Debbie wrote: Hi, I need to count the number of cells in a range that have text in the cell. There are a number of blank cells that when I use the counta function are included in the count. I don't want the blank cells counted. Any suggestions? Ex: A1 contains 'oranges';A2 to A4 is blank; A5 contains 'apples'; A6 contains 'oranges' A7 contains 'pears' I want the total in cell A8 to be 4. |
|
#5
|
|||
|
|||
|
It could be a number of things. Send me your file and
I'll take a look. Replace OPPOSITEOFCOLD with you know what. Jason -----Original Message----- Thanks Jason, but this is still returning 7, what am I doing wrong?? "Jason Morin" wrote: =SUMPRODUCT(--(LEN(A1:A7)0)) HTH Jason Atlanta, GA -----Original Message----- Hi, I need to count the number of cells in a range that have text in the cell. There are a number of blank cells that when I use the counta function are included in the count. I don't want the blank cells counted. Any suggestions? Ex: A1 contains 'oranges';A2 to A4 is blank; A5 contains 'apples'; A6 contains 'oranges' A7 contains 'pears' I want the total in cell A8 to be 4. . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Searching for text in cells | Excel Discussion (Misc queries) | |||
| how do i add the same text after current text in multiple cells | Excel Discussion (Misc queries) | |||
| Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
| How do I get merged cells to display all text. | Excel Discussion (Misc queries) | |||
| blank cells that include text | Excel Worksheet Functions | |||
Linear Mode
