count cells containing text

[Lisa W]

Hi,
I am trying to find a formula that will give me a total of the cells from certain columns in each row which contain text, for example:

I have text in some cells in columns a to f in row 1. I would like a total to appear in g1 telling me how many cells contain text from columns a, c & e excluding b, d & f. (The columns I want the totals from will be consistant for each row.)

We have tried some formulas and cant get it to work. Don't know if we are using the wong ones or if we are putting them in incorrectly.

Thanks
Lisa

[Otto Moehrbach]

What do the other cells contain? Numbers? Blank? HTH Otto
"Lisa W" <[email protected]> wrote in
message news:[email protected]...
>
>
>
> Hi,
> I am trying to find a formula that will give me a total of the cells
> from certain columns in each row which contain text, for example:
>
> I have text in some cells in columns a to f in row 1. I would like a
> total to appear in g1 telling me how many cells contain text from
> columns a, c & e excluding b, d & f. (The columns I want the totals
> from will be consistant for each row.)
>
> We have tried some formulas and cant get it to work. Don't know if we
> are using the wong ones or if we are putting them in incorrectly.
>
> Thanks
> Lisa
>
>
> --
> Lisa W
> ------------------------------------------------------------------------
> Lisa W's Profile:
> http://www.excelforum.com/member.php...o&userid=32497
> View this thread: http://www.excelforum.com/showthread...hreadid=522894
>

[Lisa W]

Hopefully this is what you asked for??
For the actual worksheet I am working on:
*columns a to k contain text and numbers or blanks - none of this data is wanted for totals
*columns l,p,t,x,ab... contain text or are blank - these are the ones we want the totals at the end of each row ("L to O" is a set of data, so is "p to s" and so on. We want to know how many of these sets occur in each row - if the beginning column (l, p etc) contains text then the following 3 will too, otherwise blank

Thanks!
Lisa

[Harlan Grove]

Lisa W wrote...
>I am trying to find a formula that will give me a total of the cells
>from certain columns in each row which contain text, for example:
>
>I have text in some cells in columns a to f in row 1. I would like a
>total to appear in g1 telling me how many cells contain text from
>columns a, c & e excluding b, d & f. (The columns I want the totals
>from will be consistant for each row.)
....

For relatively few nonadjacent columns, simplest to use something like

=ISTEXT(A3)+ISTEXT(C3)+ISTEXT(E3)

This would count cells evaluating as "" rather than blank as text. If
you want to count only cells containing one or more characters of text,
try

=COUNTIF(A3,"?*")+COUNTIF(C3,"?*")+COUNTIF(E3,"?*")

[Gary''s Student]

This may be helpfull if you can adapt it:

If you have any range (rows, columns, etc.) you can count text cells by
using COUNTA() and COUNT(). For example if you want the count of text cells
in column A from A1 to A500 use:

=COUNTA(A1:A500)-COUNT(A1:A500)


You can apply this to individual columns and then add the results
--
Gary''s Student


"Lisa W" wrote:

>
> Hopefully this is what you asked for??
> For the actual worksheet I am working on:
> *columns a to k contain text and numbers or blanks - none of this data
> is wanted for totals
> *columns l,p,t,x,ab... contain text or are blank - these are the ones
> we want the totals at the end of each row ("L to O" is a set of data,
> so is "p to s" and so on. We want to know how many of these sets occur
> in each row - if the beginning column (l, p etc) contains text then the
> following 3 will too, otherwise blank
>
> Thanks!
> Lisa
>
>
> --
> Lisa W
> ------------------------------------------------------------------------
> Lisa W's Profile: http://www.excelforum.com/member.php...o&userid=32497
> View this thread: http://www.excelforum.com/showthread...hreadid=522894
>
>