Home |
Search |
Today's Posts |
#1
|
|||
|
|||
counting total cells with text
Hi , I have a column with text and blanks and I am trying to count the total number of cells with text. I searched through this site and found something with COUNTA() - COUNT() but this doesn't work because I have no numerical values. Any ideas are sincerely appreciated Thank you -- peace ------------------------------------------------------------------------ peace's Profile: http://www.excelforum.com/member.php...o&userid=27035 View this thread: http://www.excelforum.com/showthread...hreadid=444459 |
#2
|
|||
|
|||
One way =ROWS(A1:A20)-COUNTBLANK(A1:A20) -- bill k ------------------------------------------------------------------------ bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821 View this thread: http://www.excelforum.com/showthread...hreadid=444459 |
#3
|
|||
|
|||
Your original formula will work even tho there are no numerical values,
however, this will do it by itself. (Substitute your column designation for A:A) =COUNTA(A:A) Vaya con Dios, Chuck, CABGx3 "peace" wrote in message ... Hi , I have a column with text and blanks and I am trying to count the total number of cells with text. I searched through this site and found something with COUNTA() - COUNT() but this doesn't work because I have no numerical values. Any ideas are sincerely appreciated Thank you -- peace ------------------------------------------------------------------------ peace's Profile: http://www.excelforum.com/member.php...o&userid=27035 View this thread: http://www.excelforum.com/showthread...hreadid=444459 |
#4
|
|||
|
|||
Bear in mind that COUNTA(A:A) will return not only the number of cells that contain text but also the number of cells that contain formulas. If you have formulas returning the "" result to make your cells look like they're blank then you won't be getting an accurate count of cells containing text only. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=444459 |
#5
|
|||
|
|||
Thanks to all those who responded. Cutter is right, counting cells with formulas that return blank values gives the wrong total number of cells with text. Is there some sort of trick I can use for this? Thanks again -- peace ------------------------------------------------------------------------ peace's Profile: http://www.excelforum.com/member.php...o&userid=27035 View this thread: http://www.excelforum.com/showthread...hreadid=444459 |
#6
|
|||
|
|||
One solution is to have a helper column somewhere with the formula: =IF(LEN(A1)0,1,0) Copy the formula down as far as is needed. Then do a SUM() on that helper column -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=444459 |
#7
|
|||
|
|||
Try...
=COUNTIF(A1:A100,"?*") or =COUNTIF(A:A,"?*") Hope this helps! In article , peace wrote: Thanks to all those who responded. Cutter is right, counting cells with formulas that return blank values gives the wrong total number of cells with text. Is there some sort of trick I can use for this? Thanks again |
#8
|
|||
|
|||
sorry to butt in, but I have the same issue, well almost, how do you count only the cell that has specific text? Like count only cells with "7p" and "7a" but not "off", or "sick" or "vacation"? Thanks. -- cardingtr ------------------------------------------------------------------------ cardingtr's Profile: http://www.excelforum.com/member.php...o&userid=27027 View this thread: http://www.excelforum.com/showthread...hreadid=444459 |
#9
|
|||
|
|||
=COUNTIF(A:A,"7p")+COUNTIF(A:A,"7a")
Vaya con Dios, Chuck, CABGx3 "cardingtr" wrote in message ... sorry to butt in, but I have the same issue, well almost, how do you count only the cell that has specific text? Like count only cells with "7p" and "7a" but not "off", or "sick" or "vacation"? Thanks. -- cardingtr ------------------------------------------------------------------------ cardingtr's Profile: http://www.excelforum.com/member.php...o&userid=27027 View this thread: http://www.excelforum.com/showthread...hreadid=444459 |
#10
|
|||
|
|||
Thanks you! CLR Wrote: =COUNTIF(A:A,"7p")+COUNTIF(A:A,"7a") Vaya con Dios, Chuck, CABGx3 "cardingtr" wrote in message ... sorry to butt in, but I have the same issue, well almost, how do you count only the cell that has specific text? Like count only cells with "7p" and "7a" but not "off", or "sick" or "vacation"? Thanks. -- cardingtr ------------------------------------------------------------------------ cardingtr's Profile: http://www.excelforum.com/member.php...o&userid=27027 View this thread: http://www.excelforum.com/showthread...hreadid=444459 -- cardingtr ------------------------------------------------------------------------ cardingtr's Profile: http://www.excelforum.com/member.php...o&userid=27027 View this thread: http://www.excelforum.com/showthread...hreadid=444459 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text wrap in merged cells | Excel Discussion (Misc queries) | |||
format cells having text | Excel Discussion (Misc queries) | |||
Counting the total number of cells with specified condition(freque | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Excel - counting cells containing a text string | Excel Worksheet Functions |