Counting completed items, only if relevant

Options
Neil Watson
Neil Watson ✭✭✭✭✭✭
edited 10/14/20 in Formulas and Functions

I have rows of items. Each row starts with 2 columns - Done and Not relevant.

When someone actions a row, they tick off the checkbox under "Done".

If the row is not applicable, they tick off the checkbox under "Not relevant".

The rows are children of a Header row which counts the rows completed using the formula ="Completed: " + COUNTIF(CHILDREN(Done7), 1) + " of " + ((COUNTIF(CHILDREN(Done7), 1) + (COUNTIF(CHILDREN(Done7), 0))))

I want to exclude from this count the rows that are ticked as "Not relevant"

Is this possible?

Best Answer

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    I think you would just need to make your COUNTIF into COUNTIFS. It would look like:

    ="Completed: " + COUNTIFS(CHILDREN(Done@row), 1, CHILDREN([Not relevant]),0) + " of " + ((COUNTIFS(CHILDREN(Done7), 1, CHILDREN([Not relevant]7), 0)) + (COUNTIFS(CHILDREN(Done7), 0, CHILDREN([Not relevant]7),0)))))

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    Options

    Thanks David, much appreciated

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    No problem, glad it works.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!