Why is this Google Sheets formula counting blank cells?

Question

I am new to Google Sheets but really am only using it for simple tasks - yet I have encountered an issue that I cannot seem to resolve. I will start with an image of my test Google Sheet below:

If you take a look at the above picture, this is a screenshot of a test sheet that is exhibiting the issue that I don't know how to resolve. Column A is using the formula =IF(E3<>"",COUNTA($E$3:E3),"") to count and number the values in Column E.

I then copied and pasted the values from Column A to Column C, and attempted to do the same calculation in Column B on Column C, with some changes to the values. The same formula (now placed into Column B) IS counting the values, but is no longer skipping the blank cells. Does anybody know why this is? I'm guessing it has something to do with "Edit > Paste Special > Paste Values Only" not working as I would think it should work, as something is now inside the blank columns of Column C that I cannot see. Or maybe my understanding of the formula is off?

Summary: Why is the formula: =IF(E3<>"",COUNTA($E$3:E3),"") working between Column A and Column E, but not working the same way between Column B and Column C? I would like it to count the values AND skip the blank cells. This picture is a small scale version of a much larger google sheet, but the problem is identical. I need to be able to edit a column and then recount/renumber the values.

PS: I tried to share this actually Google Sheet but Google won't let me share it with just anybody, as I have to grant access to specific people. If somebody would like to inspect it, I can export it and upload it to a Google Drive.

EDIT: I thought it would be helpful to clearly add what I am looking for.

I would like the formula in Column B to skip the blank cells in Column C, outputting 13 at the last cell (Cell B24) instead of 22.

Answer 1

Instead of

=IF(E3<>"",COUNTA($E$3:E3),"") 

use

=IF(ISBLANK(E3),COUNTA($E$3:E3),) 

Explanations

• "" is an empty text value, COUNTA counts it as a value.
• ISBLANK returns TRUE if the reference is blank (meaning it hasn't any value at all). It's similar to nullin some programming languages but not exactly the same.
• Google Sheets, as well as other spreadsheets parse an empty cell as 0 when compared with numbers or used in arithmetic calculations and as an empty string when used in TEXT operations like concatenating two values.

Related

• Return a blank when cell reference is blank
• Why is Google Sheets counting this cell as both blank and not blank?