CODEWITHSUNDEEP
google-sheets
Andres
Andres

Reputation: 73

Is COUNTA counting blank (empty) cells in new Google spreadsheets?

In the new google spreadsheets, COUNTA is counting blank cells when referencing empty cells that are part of an arrayformula (within an arrayformula column). This is not the case with the old google spreadsheets, where COUNTA gives the expected result (0). Are other formulas equally affected? Any workaround?

Upvotes: 5

Views: 7626

Answers (3)

user17328900
user17328900

Reputation: 1

I just ran into this exact problem and was looking for an easier fix. I simply highlighted the fields effected by this and used the "replace" function. I left the first cell in the function blank and replaced it with a #1 (this was not used in any of my existing cells, as they were words not numbers). I then used the replace again and replaced all the 1's with a blank cell. It worked!

Upvotes: 0

AdamL
AdamL

Reputation: 24609

Yes, there has been a change in behaviour in how COUNTA deals with zero-length text strings, in the new version of Sheets.

In the new google spreadsheets, COUNTA is counting blank cells when referencing empty cells that are part of an arrayformula (within an arrayformula column). This is not the case with the old google spreadsheets, where COUNTA gives the expected result (0).

Presumably the empty cells you refer to are being populated with a "" in your array formula. In the new version of Sheets, these cells are counted, and you are correct that in the old version they were not. For what it's worth, the new behaviour is consistent with what occurs in MS Excel.

Are other formulas equally affected?

Yes, the SORT function (and performing an in-situ sort with the built-in sorting tools) will now sort a zero-length text string after numbers and dates, and before other text strings, whereas before the zero-length text strings were pushed to the bottom of a sort (but before truly blank cells). Again, this new sorting behaviour is consistent with MS Excel.

Any workaround?

I think the best workaround would be to drop the usage of "" in IF formulae. In the new version of Sheets, if you omit the second or third argument of an IF function altogether, and the function evaluates to that argument, then a truly blank cell is returned (at the time of writing this, anyway). So instead of using something like:

=ArrayFormula(IF(A2:A>25, "something",""))

use:

=ArrayFormula(IF(A2:A>25, "something",))

and the COUNTA won't count the blank cells.

Upvotes: 6

roirraW 'edor' ehT
roirraW 'edor' ehT

Reputation: 91

Although dropping the third argument will work if you only have one If statement in an ArrayFormula, if you have nested Ifs that's not a choice (since there is no OR or AND working in ArrayFormula). However, from CountIF for non-blank cells in the Google Docs forum I got the idea to try the following:

countif(P4:P,">""")

and it worked perfectly.

Upvotes: 8

Related Questions