Formula to count exclude a response within a column

I'm trying to create a formula to count the total number of cells within a column with "complete" and "incomplete" excluding the responses with "N/A". I attempted this formula but it did not return the correct value ("Unparseable"): =COUNTIFS([Action Plan Compliance]:[Action Plan Compliance], ="Complete", [Action Plan Compliance]:[Action Plan Compliance], ="Incomplete"). This is a screen shot of the column.



Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @jjluna

    The current formula you have is looking for if a cell has both of those options at the same time. Instead, you'll want to have two separate COUNTS and then add them together:

    =COUNTIFS() + COUNTIFS()

    For example:

    =COUNTIFS([Action Plan Compliance]:[Action Plan Compliance], "Complete") + COUNTIFS([Action Plan Compliance]:[Action Plan Compliance], "Incomplete")

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @jjluna

    The current formula you have is looking for if a cell has both of those options at the same time. Instead, you'll want to have two separate COUNTS and then add them together:

    =COUNTIFS() + COUNTIFS()

    For example:

    =COUNTIFS([Action Plan Compliance]:[Action Plan Compliance], "Complete") + COUNTIFS([Action Plan Compliance]:[Action Plan Compliance], "Incomplete")

    Cheers,

    Genevieve

  • jjluna
    jjluna ✭✭

    Thank you, Genevieve!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!