Formula to count tasks completed (x out of x).

Options

Hi Smartsheet Community,

I have used the remote onboarding template set to help track how many tasks have been completed. I am now working on a new sheet where my requirements vary and I need to count the completed tasks in a different manner by using text instead of a checkbox. I am fairly new to this part of Smartsheets, so any help in understanding how to get to the solution (or use a summary sheet) is greatly appreciated!

Here is a link to the remote onboarding template. Instead of a checklist column labeled "done", I now have a column labeled "task status" that counts the percentage of the project completed when a cell is marked as complete. I am trying to make a new column labeled "count of tasks completed" to show "x out of total tasks" completed.

Below are the formulas I am using on the first, second, and third rows for the column labeled "% Complete"

1st row: =AVG(CHILDREN())

2nd row: =AVG(CHILDREN())

3rd row: =COUNTIF(CHILDREN(Status@row), ="Complete") / COUNT(CHILDREN(Status@row))

Thank you in advance!

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Namaste03

    You've already done the heavy lifting for the formulas. You can take your 3rd row formula to use it in your x of y completed formula. Since you will be mixing texts and numbers together, the VALUE function will alert smartsheet of which characters are numeric.

    ="x out of total tasks" 

    =VALUE(COUNTIF(CHILDREN(Status@row), ="Complete")) + " completed out of "+VALUE(COUNT(CHILDREN(Status@row)))+" total tasks"

    Does this work for you?

    Kelly

  • Namaste03
    Namaste03 ✭✭
    Answer ✓
    Options

    It works! Thank you Kelly.

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Namaste03

    You've already done the heavy lifting for the formulas. You can take your 3rd row formula to use it in your x of y completed formula. Since you will be mixing texts and numbers together, the VALUE function will alert smartsheet of which characters are numeric.

    ="x out of total tasks" 

    =VALUE(COUNTIF(CHILDREN(Status@row), ="Complete")) + " completed out of "+VALUE(COUNT(CHILDREN(Status@row)))+" total tasks"

    Does this work for you?

    Kelly

  • Namaste03
    Namaste03 ✭✭
    Answer ✓
    Options

    It works! Thank you Kelly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!