counting total cells with text

[peace]

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

[bill k]

One way
=ROWS(A1:A20)-COUNTBLANK(A1:A20)

[CLR]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Cutter]

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.

[peace]

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

[Cutter]

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

[Domenic]

Try...

=COUNTIF(A1:A100,"?*")

or

=COUNTIF(A:A,"?*")

Hope this helps!

In article <[email protected]>,
peace <[email protected]> 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

[cardingtr]

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.

[CLR]

=COUNTIF(A:A,"7p")+COUNTIF(A:A,"7a")

Vaya con Dios,
Chuck, CABGx3


"cardingtr" <[email protected]> wrote
in message news:[email protected]...
>
> 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]

Thanks you!

=COUNTIF(A:A,"7p")+COUNTIF(A:A,"7a")

Vaya con Dios,
Chuck, CABGx3


"cardingtr" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>