0

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:

my test google sheet

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.