This one really intrigues me. I am trying to figure out the difference
between a blank cell and an empty cell. A cell that has not been
touched in excel evaluates to zero. It will also evaluate to
ISBLANK(); TRUE. If I enter random data in a cell press return and
then delete that data the cell evaluates the same way.

This tells me that there is no difference between a blank cell and an
empty cell.

FWIW if you use the following formula to evaluate a cell you get the
#VALUE error.

=if(A1=(char(0)),true.false)

Char(0) is the ascii value for null (or blank if you prefer).

You even get the #VALUE error if you place the following formula in
cell A1
=char(0).

So as far as I can tell there is no difference between an Empty cell
and a blank cell. And Excel does not designate empty cells as ascii
character 0. And Excel treats an empty cell the same way as a the
number 0. To further expand on this I did the following.

Starting on a new untouched worksheet.

I put the following formulas in cells b1-d1 respectively and filled
down for 6 cells.

=IF(A1=0,1,0) =IF(ISNUMBER(A1),1,0) =IF(ISBLANK(A1),1,0) =IF(A1="0",1,0)

I put the following in cells A1-A6
nothing
=0
="0"
0
entered a number and then deleted
entered text and then deleted

the results of the formulas in cells B-E are
1 0 1 0
1 1 0 0
0 0 0 1
0 0 0 1
1 0 1 0
1 0 1 0

Don't know if this helps anyone but it was an interesting academic
excersize.


Gary

Biff Wrote:
Clarification:

That doesn't work.........

If the cells are EMPTY, it does work if the cells have formula BLANKS.

Don't know whether the OP meant "blank" or empty.

How would you count EMPTY cells? I haven't been able to figure it out.

Biff

"Biff" wrote in message
...
That doesn't work. Result = 0, unfiltered or filtered. I tried that
formula (along with many other variations) earlier.

Typo in the formula, one too many ")"

ROW(B5)),,1))

ROW(B5),,1))

Biff

"Aladin Akyurek" wrote in message
...
Assuming that the secret column range is B5:B200...


=SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B200)-ROW(B5)),,1)),--(B5:B200=""))

Gos-C wrote:
Hi,

Is there a function to count blanks in a filtered column? That is,
if
I filter a column to show the rows that are blank, can I use the
subtotal function (or some other) to count the number of blanks?

Thanks,
Gos-C




--
GaryE
Posted from - http://www.officehelp.in