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

Find more posts tagged with

Formulas

Comments

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