counting empty cells VS spacebar and backspace

[lezawang]

Hi
I have column like the one below and at the bottom I have a function that count how many empty cells

= countif(A1:A10, "")

so Initially I have 3 cells empty. when I tried to empty a cell content in that range using Spacebar, the countif() would not count that cell but if I delete the content using Backspace then countif() will count that new empty cell.

I changed the function to the following =countif(A1:A10," ") , created space between the " "
what happened next is amazed me, now if I use the Backspace to delete cell content then countif() would not count that cell but if I use Spacebar to delete the cell content then countif() wil count that empty cell. So it is basically reverse of the first scenario.

Any idea why. Thank you so much.

 

[JackDanIce]

A space is a character, it's just one you can't see.

COUNTIF(A1:A10,"") won't count it because it has a character of length unit 1, i.e. " "

COUNTIF(A1:A10, " ") does count cells that appear to be empty if they have a space character in it, because that space character is of length 1 and matches the count if condition e.g. it is not COUNTIF(A1:A10,"a"), the string "a" vs string " " of space.

Put a mix of empty cells, cells with spaces and cells with text into A1:A10
In B1 enter formula "=LEN(A1)", drag this formula to B10
Evaluate the results

 

[TonyUK72]

If users of your sheet "might" use the space bar to delete the cell contents, you can use TRIM() in an adjacent cell to remove leading spaces.