count cells containing text

L

Lisa W

:confused:

Hi,
I am trying to find a formula that will give me a total of the cell
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
total to appear in g1 telling me how many cells contain text fro
columns a, c & e excluding b, d & f. (The columns I want the total
from will be consistant for each row.)

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

Thanks :)
Lis
 
L

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 dat
is wanted for totals
*columns l,p,t,x,ab... contain text or are blank - these are the one
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 occu
in each row - if the beginning column (l, p etc) contains text then th
following 3 will too, otherwise blank

Thanks!
Lisa:
 
L

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 dat
is wanted for totals
*columns l,p,t,x,ab... contain text or are blank - these are the one
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 occu
in each row - if the beginning column (l, p etc) contains text then th
following 3 will too, otherwise blank

Thanks!
Lisa:)
 
H

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,"?*")
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top