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

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

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

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

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

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

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

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

=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

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