Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

CountIF "No" or Blank

tobrien3
tobrien3 ✭
edited 12/09/19 in Archived 2016 Posts

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

 

Tags:
· Share on FacebookShare on Twitter

Comments

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

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

    · Share on FacebookShare on Twitter
  • Greg Gates
    Greg Gates ✭✭✭✭✭
    edited 09/15/16

    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.

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    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

     

     

     

     

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories