Count Nonblank Text Cells

T

TKrepitch

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! :)
 
A

Andy

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! :)
Click to expand...

Assuming your data is in columns A to E

=COUNTA(A:E)

Andy
 
H

HoseHead78

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

Code:
--------------------
=COUNTA(A2:A6)
--------------------


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

Code:
--------------------
=COUNTA(A2:A3, A6)
--------------------


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

Hope this helps
 
D

davesexcel

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

TKrepitch

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!
 
D

Dave Peterson

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.
 
T

TKrepitch

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!
 
T

TKrepitch

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.
 
D

Dave Peterson

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
 
H

Harlan Grove

[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?
Click to expand...

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

Dave Peterson

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.
 
T

TKrepitch

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. :)
 
H

Harlan Grove

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.
Click to expand...
....

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)." "))<>""))
 
D

Dave Peterson

Just a typo alert:

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

but, yes, I was assuming that.
 
Top