16

I have a formula an iferror formula that puts in "" if an error occurs. This is a zero length string. I'd like to do a count if not equal to "". 

=countif(A:A,<>"") 'is not a valid formulas
=countif(A:A,"<>") 'checks for actual blanks, not zero length strings
user2242044
  • 8,803
  • 25
  • 97
  • 164

3 Answers3

37

Rather than using COUNTBLANK and subtracting from the total, you can use:

=COUNTIF(A:A,"?*")

? is the single character wildcard.
* is the multiple character wildcard.
Combining these two, it will count if there are 1 or more characters.

Note that this works only if the cells contains strings, not numbers.

jgawrych
  • 3,322
  • 1
  • 28
  • 38
17

You could perhaps use SUMPRODUCT since you have limited control over the range or criteria.

=SUMPRODUCT(--(LEN(A:A)<>0))

LEN(A:A)<>0 checks the length of the strings in the range A:A for whether they are 0 or not. Wrapping it in parens and putting -- before it will convert True to 1 and False to 0.

SUMPRODUCT then takes all the 1s and 0s and add them up.

Jerry
  • 70,495
  • 13
  • 100
  • 144
7

Do a count for empty cells using:

=COUNTBLANK(A2:B5)

and subtract that value from the total.

António Almeida
  • 9,620
  • 8
  • 59
  • 66
kurast
  • 1,660
  • 3
  • 17
  • 38
  • 2
    That would work but it introduces an extra function. It seems like an oversight there is no way to do this in a single function. – user2242044 May 16 '14 at 17:48
  • 1
    Also, most excel functions do not diferentiate between empty strings and real blank cells, this is not easy to do – kurast May 16 '14 at 17:55