Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Cells With Text
I would like to be able to count the number of cells within a range that
contain text, excluding those that are strictly numeric values. For example, counting: 34 52 n/a 987 ooops 4 would return the result of 2. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Cells With Text
DonArturo wrote:
I would like to be able to count the number of cells within a range that contain text, excluding those that are strictly numeric values. For example, counting: 34 52 n/a 987 ooops 4 would return the result of 2. Array formula (commit with CTRL+SHIFT+ENTER): =SUM(--ISTEXT(A1:A6)) Keep in mind that this will also count cells that look like numbers but are actually entered as text. As an example, enter '52 instead of 52 in the data above. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Cells With Text
Hi,
Try this =COUNTA(A1:A6)-COUNT(A1:A6) Mike "DonArturo" wrote: I would like to be able to count the number of cells within a range that contain text, excluding those that are strictly numeric values. For example, counting: 34 52 n/a 987 ooops 4 would return the result of 2. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Cells With Text
Mike H wrote...
Try this =COUNTA(A1:A6)-COUNT(A1:A6) .... This counts all entries except numbers, which means it'd count boolean and error values in addition to text. Simplest way to count cells containing text is =COUNTIF(range,"*") which would include nonblank cells evaluating to "". To count only text with one or more characters, use =COUNTIF(range,"?*") |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Cells With Text
Thanks guys. All three solutions worked as you said.
Harlan: Using =COUNTIF(range,"*") was especially helpful because it counted both the text cells and the "" or null cells, which is what I needed. That is, it counted them in the simple COUNTIF version. Using COUNTIFS, however, produced a different result because I must put the equals inside the quotes. Here is the actual formula: =COUNTIFS($D$2:$D$1600,AE1,$K$2:$K$1600,"0",$N$2: $N$1600,"=*") In this instance, it only counts the text cells and not the "" or null cells. My workaround is to also use =COUNTIFS($D$2:$D$1600,AE1,$K$2:$K$1600,"0",$N$2: $N$1600,"") and add them together. Unless you know a better way... :) Thanks again. Peace _________________________________________ "Harlan Grove" wrote: Mike H wrote... Try this =COUNTA(A1:A6)-COUNT(A1:A6) .... This counts all entries except numbers, which means it'd count boolean and error values in addition to text. Simplest way to count cells containing text is =COUNTIF(range,"*") which would include nonblank cells evaluating to "". To count only text with one or more characters, use =COUNTIF(range,"?*") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting cells with text not listed. | Excel Worksheet Functions | |||
Counting cells that don't contain certain text | Excel Worksheet Functions | |||
Counting cells with text | Excel Worksheet Functions | |||
Counting Occurrence of Text within Text in Cells in Range. | Excel Worksheet Functions | |||
counting text cells | Excel Worksheet Functions |