Count Nonblank Text Cells

Hello. I searched for a solution to this, but couldn't quite find what
I needed.

I am importing data into five columns in Excel. Each of the cells is
formatted as text, but most of them are blank. I want a count of the
ones that are not blank.

I have five columns for problem codes, so each row will have between
zero and five problem codes. This is a little trickier than I thought
because the problem codes can be either numbers or letters or a
combination of both.

How do I write a formula that says, "Look in these five cells and tell
me how many of them have something meaningful in them"?

Any advice is appreciated. Thanks!

You can use the Function COUNTA.
Counts the number of nonblank cells in the list above (3)

Please Login or Register  to view this content.

Counts the number of nonblank cells in the top two, and bottom cell in the list (1)

Please Login or Register  to view this content.

This can also be found by pressing F1 for help and searching for COUNTA

Hope this helps

<[email protected]> wrote in message
news:[email protected]...
> Hello. I searched for a solution to this, but couldn't quite find what
> I needed.
>
> I am importing data into five columns in Excel. Each of the cells is
> formatted as text, but most of them are blank. I want a count of the
> ones that are not blank.
>
> I have five columns for problem codes, so each row will have between
> zero and five problem codes. This is a little trickier than I thought
> because the problem codes can be either numbers or letters or a
> combination of both.
>
> How do I write a formula that says, "Look in these five cells and tell
> me how many of them have something meaningful in them"?
>
> Any advice is appreciated. Thanks!

Assuming your data is in columns A to E

=COUNTA(A:E)

Andy

=COUNTA(1:1) counts non blanks in a row
=COUNTA(G1:K11) counts non blanks in a range

Thanks, guys. COUNTA was what I tried, but I think the issue has to do
with how the cells are formatted.

It's that issue where they look blank, but really aren't (hard for me
to describe). If I go to a cell that looks blank and press delete, the
result of the COUNTA function changes.

That is, right now it results in 5 for each row, but if I delete out
the "content" of one of the "blank" cells, it results in 4.

So I need to figure out what exactly I am doing when I "delete" content
in a cell and how to get a formula to do it.

Thanks!

If you put:
=len(a1)
(use an offending cell)

do you get 0?

If you get something larger than 0, you could have a space character in that
cell (or multiple space characters in the cell).

If that formula shows 0, then try this:

select all those cells
edit|replace
what: (Leave blank)
with: $$$$$
replace all

Then do it again
edit|replace
what: $$$$$
with: (leave blank)
replace all

This clears up the junk left behind when you convert formulas like this:

=if(x99="a","","ok")
that have had edit|copy, edit|paste special|values done to them.

[email protected] wrote:
>
> Thanks, guys. COUNTA was what I tried, but I think the issue has to do
> with how the cells are formatted.
>
> It's that issue where they look blank, but really aren't (hard for me
> to describe). If I go to a cell that looks blank and press delete, the
> result of the COUNTA function changes.
>
> That is, right now it results in 5 for each row, but if I delete out
> the "content" of one of the "blank" cells, it results in 4.
>
> So I need to figure out what exactly I am doing when I "delete" content
> in a cell and how to get a formula to do it.
>
> Thanks!

--

Dave Peterson

The len function gives me 2 when there is something readable in the
cell and 1 when it is "blank".

Is there any way to use a formula to strip out the blanks, so I won't
need to use the replace option?

Thanks!

Oh, the error codes are two digits, so that is where the 2s are coming
from. And the 1s, of course, must be something that the program sees
that I can't.

I think I'd try this first:

Select all the cells to fix
edit|replace
what: (2 space characters)
with: (leave blank)
make sure you have "match entire cell contents" checked
replace all

And do the same thing with a single space character.

(and maybe 3 or 4 or 5 or...)

You may want to try David McRitchie's routine to clean the data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

[email protected] wrote:
>
> The len function gives me 2 when there is something readable in the
> cell and 1 when it is "blank".
>
> Is there any way to use a formula to strip out the blanks, so I won't
> need to use the replace option?
>
> Thanks!

--

Dave Peterson

[email protected] wrote...
>The len function gives me 2 when there is something readable in the
>cell and 1 when it is "blank".
>
>Is there any way to use a formula to strip out the blanks, so I won't
>need to use the replace option?

If you want to count the cells in a range (RNG) that have 2 (or more)
characters in them, you could use the formula

=COUNTIF(RNG,"??*")

One more way, although I always think that it's better to clean up the data:

=SUMPRODUCT(--(TRIM(A1:A10)<>""))

Adjust the range, but don't use the whole column.

[email protected] wrote:
>
> The len function gives me 2 when there is something readable in the
> cell and 1 when it is "blank".
>
> Is there any way to use a formula to strip out the blanks, so I won't
> need to use the replace option?
>
> Thanks!

--

Dave Peterson

Thanks, everybody. I am going to use =COUNTIF(RNG,"??") for now and
try working on cleaning up the data if I have to do something similar
to this again. I appreciate all of your help.

Dave Peterson wrote...
>One more way, although I always think that it's better to clean up the data:
>
>=SUMPRODUCT(--(TRIM(A1:A10)<>""))
>
>Adjust the range, but don't use the whole column.
....

You're assuming the nongraphic char is an ASCII space. If it's an HTML
nonbreaking space, this would need to be expanded to

=SUMPRODUCT(--(TRIM(SUMSTITUTE(A1:A10,CHAR(160)." "))<>""))

Just a typo alert:

=SUMPRODUCT(--(TRIM(SUMSTITUTE(A1:A10,CHAR(160)," "))<>""))

but, yes, I was assuming that.

Harlan Grove wrote:
>
> Dave Peterson wrote...
> >One more way, although I always think that it's better to clean up the data:
> >
> >=SUMPRODUCT(--(TRIM(A1:A10)<>""))
> >
> >Adjust the range, but don't use the whole column.
> ...
>
> You're assuming the nongraphic char is an ASCII space. If it's an HTML
> nonbreaking space, this would need to be expanded to
>
> =SUMPRODUCT(--(TRIM(SUMSTITUTE(A1:A10,CHAR(160)." "))<>""))

--

Dave Peterson