"blank cells"

[timetabler]

When I paste a data set into excel, I want to make calculations based on the number of cells that are blank.
However, some cells look blank, have no formula, but are not "blank", therefore countblank gives an incorrect number, I have checked there is no formula or single space in the cell.
When I select one or multiple cells and press delete, the countblank returns the correct number of blank cells.

Any ideas what is causing the "blank" cells to return that they are not, and if there is a shortcut to resolving the problem (even if I have to check each time I paste in).

 

[Richard Schollar]

You could maybe use:

=SUMPRODUCT(--(LEN(Range)=0))

instead. Note that this will count cells containing a formula resulting in "" as blank though.

 

[John Davis]

When I paste a data set into excel, I want to make calculations based on the number of cells that are blank.
However, some cells look blank, have no formula, but are not "blank", therefore countblank gives an incorrect number, I have checked there is no formula or single space in the cell.
When I select one or multiple cells and press delete, the countblank returns the correct number of blank cells.

Any ideas what is causing the "blank" cells to return that they are not, and if there is a shortcut to resolving the problem (even if I have to check each time I paste in).

Could there be more than a single space in each cell ? Select your range Edit and Replace - what (space) with ""