Count non-blank cells?

[Matt]

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?

 

[muddan madhu]

=COUNTIF(A1:A100,"")

 

[T. Valko]

Try this:

=COUNTBLANK(A1:A10)

Counts both empty cells and cells that contain formula blanks.

 

[Matt]

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

 

[T. Valko]

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))

 

[Matt]

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

 

[T. Valko]

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

 

[Matt]

That is SWEET.

 

[T. Valko]

You're welcome!