CountIF "No" or Blank

tobrien3

Hi Smartsheet community,

 

Trying to create a formula that counts the number of cells that are blank or have "No" in them.

 

It is a dropdown column with only two choices, "Yes" and "No", so I tried 

 

=COUNTIF([ColumnName]:[ColumnName], <> "Yes"), but that gives me 0

 

Brett Evans

COUNTIFS([ColumnName]:[ColumnName], ="No" + COUNTIFS([ColumnName]:[ColumnName],  "")

Greg Gates

Brett's answer should do the trick! I'll just add that your idea is good - it would at least count all of the "No" cells. The only issue is that when you're evaluating <> "YES" Smartsheet ignores blank cells. I think the only way to count those is to do what Brett suggested.

J. Craig Williams

Brett's formula is missing a closing paranthesis.

Also, when checking for equal to, you can skip the = sign, like so.

 

COUNTIFS([ColumnName]:[ColumnName], "No")

 

To check for a blank, you can use the new @cell reference like so:

 

COUNTIFS([ColumnName]:[ColumnName], ISBLANK(@cell))

 

In some cases a non-value (a "null") may be different than a blank value.

I'm not sure if that is true in Smartsheet, but I vaguely recall seeing something that caused me to think it was.

 

Craig