1

Google Sheets appears to be counting my blank cell as both blank and non-blank.

The cell in question, A1, is filled with this formula: =IF(1=0,"Some output",""). The output, of course, is an empty string, since 1 does not equal 0.

Using countif, I'm running into the problem that it is counting that cell when I use both the comparison "<>" and "". So a cell with =COUNTIF(A1, "<>") returns 1, and a cell with =COUNTIF(A1, "") also returns 1.

In Excel or Libreoffice Calc, the values of the two countif functions line up with my expectations, depending on if the cell A1 has a resulting value or not. But Google Sheets can't make up its mind on if the cell is blank or not and ends up counting it both times. (It doesn't count it for both if there is a value returned, nor does it count it both times if A1 is truly empty with no formula.)

Is there something wrong with my formulas? Or is this a bug in Google Sheets? I'd like to be able to correctly count or ignore blank cells in another formula.

1

Try one of these instead:

=COUNTIF(A1,"<>"&CHAR(0))

or

=COUNTIF(A1,"<>"&NULL)

Either will return the expected result of 0 if the formula =IF(1=0,"Some output","") resides in A1.

  • adding a little more context would help others understand what they should see if they pick one of these options. – mhoran_psprep Oct 14 '18 at 12:18
  • mhoran_psprep, the OP asked why two different COUNTIF formulas were saying that his A1 formula =IF(1=0,"Some output","") was returning a result that was both blank and not blank, instead of blank. Specifically, he wanted =COUNTIF(A1, "<>") to return 0, but it was returning 1. Using either of my suggested formulas will return 0 as he hoped, by separately appending the ASCII null character code - CHAR(0) - or the NULL keyword. – Erik Tyler Oct 14 '18 at 19:10
  • 1
    And if you add that explanation the answer becomes better. – mhoran_psprep Oct 14 '18 at 20:06

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.