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

Namaste03

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.

https://app.smartsheet.com/sheets/h3Q265pHw3x8m64WvHhWgrcFp2JG7r8mGxqPRjP1?view=grid

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!

Kelly Moore

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

It works! Thank you Kelly.

Kelly Moore

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

It works! Thank you Kelly.