sum of blank and non blank

S

saturnin02

Hi, How do I count blank cells in a range which contains text and separately
how do I count cells that are not blank?
I used =countblank(range) which works but how do I do the opposite, count
cells WITH text in range?
Tx,
S
 
J

Jason Morin

=COUNTA(range)

will count all cells that are not empty in "range".

=COUNTIF(range,"*")

will count only cells that contain text.

HTH
Jason
Atlanta, GA
 
S

saturnin02

Jason, Tx for your reply.
This does not work because the "blank" cells are cells that contain a
formula to make them return ""--and the counta or countif thinks that "" is
text even if it appears empty to the eye.
That is my problem......
S
 
S

Sandy Mann

If I understand you requirements correctly try:

=SUMPRODUCT(--(Range<>""))

HTH

Sandy
 
P

Phil Platt

Hiya,

=COUNTA(range) counts non-blank cells, and =COUNT(range) counts
numbers but not text. Neither of these functions count empty cells


To count text cells only in a range that also includes numbers, I
would suggest =COUNTA(range)-COUNT(range). That would give you the
number of text cells and exclude cells containing numbers.

HTH

Phil
 
B

Biff

Phil,

COUNTA() will also count cells that appear to blank yet
are not because a formula returns "". See my other reply
for a solution. Or, if the range will *never* contain
numeric numbers:

=SUMPRODUCT(--(LEN(A1:A100)>=1))

Biff
 
S

saturnin02

This works biff.
I can't believe how complex it is to just count empty and not empty cells
sometimes.....
Anyway, besides my complaining...can u explain to me the "--" part in your
formula? I mean why...?
Tx,
S
 
B

Biff

-----Original Message-----
This works biff.
I can't believe how complex it is to just count empty and not empty cells
sometimes.....
Click to expand...

Actually, it's quite easy! The complexity is knowing what
is or is not an "empty" cell. Just because you don't see
anything in it doesn't mean it's empty! Formulas that
return "" are the classic example and have been confussing
Anyway, besides my complaining...can u explain to me the "--" part in your
formula? I mean why...?
Click to expand...

When certain functions calculate, part of that process
involves determining whether a condition is TRUE or FALSE.
For instance: ISTEXT() returns a value of either TRUE or
FALSE. Using "--" converts the TRUE or FALSE to 1 or 0.
Doing this eliminates a cycle in the calculation process
and makes it faster. This functionality can also be
manipulated for use in other situations.
 
P

Phil Platt

Good point, Biff! I did see your earlier reply but I'd assumed the
original poster was just using rows of typed-in data rather than
formula results.

Still, the point is well-taken

Phil
 
Top