How can I count blank or filled in cells?

Options

Ultimate goal: To have status column update to Complete once all cells are filled in

Problem: I'm trying to count 15 cells on a row (not a range), find out if they are all filled in, then once all the selected cells are filled in, the column I'm using for this formula will turn into a check box where I can create automation to have the status turn complete. I was getting the error of #booleanexpected but now that I made it a text column it just says #UNPARSEABLE.

Formulas I've tried: =COUNTIFS(ISBLANK([Strives to Exceed Organizational Goals]@row,(@CELL), [Ensures Accountability]@row,(@CELL), [Makes Informed Decisions]@row,(@CELL)

I've also tried formula =COUNTIFS([Service-Oriented]@row, ISBLANK([Service-Oriented]@row, [Instills Trust]@row, ISBLANK([Instills Trust]@row), [Wows the Customer]@row, ISBLANK([Wows the Customer]@row)))

This is the only way I can figure out how to count blanks or non-blanks then once it is at zero, have automation update status to Complete. (If you have better solution, please LMK)

Tags:

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Options

    Hi @MyShell222

    I would probably use something like this, just add in the additional column references alongside those in the example formulas below.

    =IF(COUNT([Service-Oriented]@row, [Service-Oriented]@row, [Instills Trust]@row, [Wows the Customer]@row, [Strives to Exceed Organizational Goals]@row,[Ensures Accountability]@row,[Makes Informed Decisions]@row) < 15, "False", "True")

    This simply counts any completed cells that have anything in them, but if all 15 are not completed it makes your checkbox stay blank, 'False'

    Or should also work

    =IF(COUNT([Service-Oriented]@row, [Service-Oriented]@row, [Instills Trust]@row, [Wows the Customer]@row, [Strives to Exceed Organizational Goals]@row,[Ensures Accountability]@row,[Makes Informed Decisions]@row) = 15, "True", "False")

    This simply counts any completed cells that have anything in them, but if all 15 are completed it makes your checkbox tick, 'True'

    Hope that helps

    Thanks

    Paul

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try this:

    =IF(COUNTIFS([First Column Name]@row:[Last Column name]@row, @cell <> "") = 15, 1)

  • MyShell222
    Options

    Hi @Paul Newcome - the first formula of yours works however it's not checking the boxes:

    Formula: =IF(COUNT([Service-Oriented]@row, [Service-Oriented]@row, [Instills Trust]@row, [Wows the Customer]@row, [Strives to Exceed Organizational Goals]@row, [Ensures Accountability]@row, [Makes Informed Decisions]@row, [Demonstrates Agility]@row, [Embraces Ambiguity]@row, [Drives Continuous Improvement]@row, [Communicates Openly]@row, [Builds Relationships]@row, [Values Learning & Growth]@row, Innovates@row, [Exhibits Courage]@row, [Connects the Dots]@row, [Funct Knowledge]@row, Application@row) < 17, "False", "True")

    Thoughts?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @MyShell222 Try using a 0 (zero) for unchecked and a 1 for checked.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!