Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel Countif Not equal to string length of zero

Tags:

excel

countif

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
like image 553
user2242044 Avatar asked May 16 '14 17:05

user2242044


2 Answers

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.

like image 118
Jerry Avatar answered Sep 20 '22 15:09

Jerry


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.

like image 26
Jonathan Gawrych Avatar answered Sep 19 '22 15:09

Jonathan Gawrych