4

I want to get a formula with COUNTIFS, like

=COUNTIF(A1:A3,"<>"&"")

such that when A1 = 2, A2 = "", A3 = empty, it returns 1.

Notes:

  1. A2 contains an empty string, as the result of a formula. A3 is a blank cell, with no formulas in it.
  2. The formula posted returns 2.
  3. I tried using various numbers of double quotes. I always get 2.
  4. I tried using &CHAR(34)&CHAR(34). I get 2.
  5. The solution posted in How do I get countifs to select all non-blank cells in Excel? is what I tried, it returns 2 (not useful).
  6. The formula would actually be =COUNTIFS(range1,cond1,range2,cond2), that is why I cannot use something like =ROWS(A1:A3)-COUNTIF(A1:A3,"") or =ROWS(A1:A3)-COUNTBLANK(A1:A3) (see this). range1 and range2 would come from expressions with INDIRECT, but that is probably not relevant.
  7. I have worked it out with =SUMPRODUCT(--(expression1),--(ISNUMBER(A1:A3))), but I am specifically asking about the possibility of using COUNTIFS. Discrimination of number vs. text (e.g.) is not relevant at this point.
  8. Blank vs. Empty string is the source of "troubles" (see, e.g., this).
  9. Excel itself is somewhat ambiguous with respect to the definition of BLANK. In my example, ISBLANK(A2) returns FALSE, but COUNTBLANK(A2) returns 1.
  10. I am not interested in a user Function.
Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • @pnuts - As per point 6, I would have two (or maybe more) conditions, so I guess I am bound to using `COUNTIFS` (unless perhaps a very particular set of `range`s and `condition`s). I used `COUNTIF` in my example to illustrate the point. Preferably no array formulas, but they would be ok for the time being. Thanks. – sancho.s ReinstateMonicaCellio Mar 27 '15 at 02:27

3 Answers3

4

Use a SUMPRODUCT function that counts the SIGN function of the LEN function of the cell contents.

    COUNTIF against zero length strings

As per your sample data, A1 has a value, A2 is a zero length string returned by a formula and A3 is truly blank.

The formula in C2 is,

=SUMPRODUCT(SIGN(LEN(A1:A3)))

4

I was having this exact problem, and I just found out about the "?*" wildcard which searches for any one or more characters, thus avoiding the empty string problem--genius! See Jonathan Gawrych's answer (posted right after the selected answer) here:

Excel Countif Not equal to string length of zero

Not sure if this works for the OP, since it looks like the value in A1 could need to be handled as a number not a string, but it might help anyone else who arrived here looking for a text-parsing solution.

user11694071
  • 41
  • 1
1

Is using SUM instead of COUNTIFS an option? If so, I've found it to be much more flexible for filtering data sets. For example:

=SUM(IF(NOT(ISBLANK(A1:A3)),IF(NOT(ISTEXT(A1:A3)),1,0),0))

(entered as an array formula). IF(NOT(ISBLANK(x))... filters out non-blanks, then IF(NOT(ISTEXT(x))... filters out non-text. Whatever survives the filters is counted by summing 1. You can add as many filters as necessary. If you wanted to filter out only empty strings but include other text entries you could use a filter like

IF(ISTEXT(x),IF(LEN(x)>0,1,0),0)

xidgel
  • 3,085
  • 2
  • 13
  • 22