How do you count empty string ("") values in a column? (By "empty string" I mean zero-length strings ("label" type cells), not blanks.)

Question

Hi,

I'm trying to count the number of cells in a column that don't contain the value "" (empty string). The column is populated by formulas that lookup values in a table, and some of those values are formulas that return "", so the lookups return labels. (They should evaluate to the same as the formula ="", but that is typed as a blank, so they don't.) 

First, I tried using COUNTA, but that counted all the values in the column (since I assumed, an empty string is still a string), so I tried using COUNTIF with this formula:

=COUNTIF($M8:$M127,"<>"&M130) 

(Since Help for COUNTIF didn't tell me how to represent an empty string within a string, I placed the formula ="" in M130 and referenced the cell.)

But that too, counted every cell in the column, instead of only the 8 cells that evaluated to non-empty strings. (I also tried =COUNTIF($M8:$M127,"<>"&"") with the same results.)

Since that didn't work, I tried testing the value in M130 with the CELL function to see what cell type is was, and =CELL("type",M130) returned "b" for blank. I thought that meant I could use =COUNTBLANK($M8:$M127) to count my empty strings and subtract them from the COUNTA total, but COUNTBLANK didn't recognise the empty string cells in my column as blanks; it returned 0.)

Then I tried testing the cells in the lookup table (specifically the ones with formulas that returned ""), but in that case, the CELL "type" (e.g., =CELL("type",H61)) returned "l" for label. (The cells in the lookup table contained =IF() formulas that returned "" if FALSE.) 

So then I tried replacing the formula ="" in M130 with =IF(FALSE, TRUE, "") to get it to match the cell type. The =CELL("type",M130) returned "l" for label this time, but COUNTIF still didn't recognize the results as equivalent. It again counted every cell in the column. When I changed the formula to "="&M130, it returned 0.

First off, why is "" treated as a blank in some formulas (=""), but as a string in others (=COUNTA, COUNTBLANK and =IF(FALSE, TRUE, "")).  I'm thoroughly confused as to why EXCEL is inconsistent about this. I couldn't find a function that returns a null (blank, not empty string), to replace the "" in the IF() function, so that the COUNTBLANK function might work.

Am I doing something wrong in the COUNTIF formula? Is there another function that does what I want? It's very difficult to find any information in Help these days.

Thanks,

Gabrielle

Answer 1

Hi,

Try this

=COUNTIF($M8:$M127,"?*")+COUNT($M8:$M127)

Hope this helps.

Answer 2

To answer your subject line, to wit:

How do you count empty string ("") values in a column? (By "empty string" I mean zero-length strings

=SUMPRODUCT(ISTEXT($M8:$M127)*($M8:$M127=""))

---

But in the body of your posting, you say the opposite, to wit:

I'm trying to count the number of cells in a column that don't contain the value "" (empty string). The column is populated by formulas that lookup values

Since the cells contain formulas, they cannot be "empty" (by Excel's definition: no value). So the following should suffice:

=SUMPRODUCT(--(LEN($M8:$M127)<>0))

Answer 3

Hi Gabrielle,

I'm trying to count the number of cells in a column that don't contain the value "" (empty string). The column is populated by formulas that lookup values in a table, and some of those values are formulas that return "", so the lookups return labels. (They should evaluate to the same as the formula ="", but that is typed as a blank, so they don't.) 

First, I tried using COUNTA, but that counted all the values in the column (since I assumed, an empty string is still a string), so I tried using COUNTIF with this formula:

=COUNTIF($M8:$M127,"<>"&M130) 

(Since Help for COUNTIF didn't tell me how to represent an empty string within a string, I placed the formula ="" in M130 and referenced the cell.)

But that too, counted every cell in the column, instead of only the 8 cells that evaluated to non-empty strings. (I also tried =COUNTIF($M8:$M127,"<>"&"") with the same results.)

Since that didn't work, I tried testing the value in M130 with the CELL function to see what cell type is was, and =CELL("type",M130) returned "b" for blank. I thought that meant I could use =COUNTBLANK($M8:$M127) to count my empty strings and subtract them from the COUNTA total, but COUNTBLANK didn't recognise the empty string cells in my column as blanks; it returned 0.)

Then I tried testing the cells in the lookup table (specifically the ones with formulas that returned ""), but in that case, the CELL "type" (e.g., =CELL("type",H61)) returned "l" for label. (The cells in the lookup table contained =IF() formulas that returned "" if FALSE.) 

So then I tried replacing the formula ="" in M130 with =IF(FALSE, TRUE, "") to get it to match the cell type. The =CELL("type",M130) returned "l" for label this time, but COUNTIF still didn't recognize the results as equivalent. It again counted every cell in the column. When I changed the formula to "="&M130, it returned 0.

First off, why is "" treated as a blank in some formulas (=""), but as a string in others (=COUNTA, COUNTBLANK and =IF(FALSE, TRUE, "")).  I'm thoroughly confused as to why EXCEL is inconsistent about this. I couldn't find a function that returns a null (blank, not empty string), to replace the "" in the IF() function, so that the COUNTBLANK function might work.

Am I doing something wrong in the COUNTIF formula? Is there another function that does what I want? It's very difficult to find any information in Help these days.

Try:****

=SUMPRODUCT(--(M8:M127<>""),--(NOT(ISBLANK(M8:M127))))

===

Regards,

Norman