# 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))

• ✭✭✭✭✭✭
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.

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

Does this work for you?

Kelly

Options

It works! Thank you Kelly.

• ✭✭✭✭✭✭
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.

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

Does this work for you?

Kelly