Count non-blank cells?
 

I would like to count the number of cells in a range that are blank
(or that appear blank). That is, it would count empty cells as well
as cells whose formula returns a blank (=""). Is this possible?

Count non-blank cells?
 

=COUNTIF(A1:A100,"")

On Jun 3, 10:37*pm, Matt wrote:
I would like to count the number of cells in a range that are blank
(or that appear blank). *That is, it would count empty cells as well
as cells whose formula returns a blank (=""). *Is this possible?

Count non-blank cells?
 

Try this:

=COUNTBLANK(A1:A10)

Counts both empty cells and cells that contain formula blanks.

--
Biff
Microsoft Excel MVP


"Matt" wrote in message
...

I would like to count the number of cells in a range that are blank
(or that appear blank). That is, it would count empty cells as well
as cells whose formula returns a blank (=""). Is this possible?

Count non-blank cells?
 

Now how about this:

How would I count the number of cells that are NOT blank? So that
even if the cell does contain a formula but still shows as blank (such
as ="") , then it is not counted as a non-blank cell

Count non-blank cells?
 

If the cells contain only TEXT entries:

=COUNTIF(A1:A10,"?*")

Or, this generic version will count any type of entry:

=SUMPRODUCT(--(LEN(A1:A10)0))

--
Biff
Microsoft Excel MVP


"Matt" wrote in message
...
Now how about this:

How would I count the number of cells that are NOT blank? So that
even if the cell does contain a formula but still shows as blank (such
as ="") , then it is not counted as a non-blank cell

Count non-blank cells?
 

=COUNTIF(A1:A10,"?*")

=SUMPRODUCT(--(LEN(A1:A10)0))

Both work perfectly. What does the "?*" mean? and what does the --
before the len function do?

Thank you!

Count non-blank cells?
 

What does the "?*" mean?

Both the ? and the * are wildcard characters.

The ? wildcard represents any *single* text character.

The * represents any number of text characters.

These wildcards only work on TEXT.

So the formula checks to make sure there is any single text character or any
number of text characters in the cells. A formula blank ("") is not any
single text character and fails the test so it's not counted.

what does the -- before the len function do?

See these:

http://xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Biff
Microsoft Excel MVP


"Matt" wrote in message
...
=COUNTIF(A1:A10,"?*")

=SUMPRODUCT(--(LEN(A1:A10)0))

Both work perfectly. What does the "?*" mean? and what does the --
before the len function do?

Thank you!

Count non-blank cells?
 

That is SWEET.

Count non-blank cells?
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Matt" wrote in message
...

That is SWEET.