I have been having problems with a large workbook which I have tracked down to an odd behaviour where a cell seems to be both blank and not blank !



There is a named range, let's call it GreenBox, and to illustrate the issue I have used two ways of calculating how many cells it contains:

The first method multiplies the number of rows by the number of columns - and gives the correct result:
=ROWS(GreenBox)*COLUMNS(GreenBox)

The second method sums the number of filled cells and the number of blank cells and gives a number of cells that is 1 greater then the first method:
=COUNTA(GreenBox)+COUNTBLANK(GreenBox)

I finally pinpointed the issue when I found one cell which returns a value of 1 from COUNTA and a value of 1 from COUNTBLANK.

I can't see how this is possible ?

If I copy the offending cell into another part of the GreenBox range, the discrepancy rises to 2 - as I would expect. No contents are visible in the offending cell and deleting its contents makes no difference. But erasing the cell completely with Clear All sorts out the discrepancy.

The discrepancy arises from time to time - I have no idea how - and I would like to undertand what is going on so that I can stop it from happening.

I have a small workbook which illustrates the issue but I can't attach it here because it has a lot of personal customisation in the ribbon which gives away personal information. I would post it if I could find a way of making this one workbook have no customisation.

Appreciate any help / ideas.