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

Question

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?

Answer 1

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.