# Count completed task if all sub-task is completed

Options
✭✭

Hi,

Hope someone can help. I've tried a few combination formulas but still not able to figure out.

I'm trying to count the Completed activities, with the condition all sub-tasks under the same activity is completed.

Example below:

Activity-AA have 2 sub-tasks.

## Answers

• ✭✭✭✭✭✭
Options

Hey @Kelvin

From your screenshot it is difficult to see what is a sub task and what is not, and if it is a sub task then what is it linked to. Are there child rows present and we cannot see the hierarchy in this view?

Kelly

• ✭✭
edited 05/04/23
Options

Thanks for responding, hope this is clearer.

Primary - Main Task

• Division/Group - sub-task

Summary: 4 Primary activities (AA, PPP, Safety, SS) & 2 Completed (Safety & SS)

Tried integrating IF/COUNTIF into below formula but doesn't work.

=COUNT(DISTINCT(COLLECT({Primary Activities}, {Status}, ="Completed")))

• ✭✭✭✭✭✭
Options

Hey @Kelvin

You'll need a helper column (a checkbox will do) so you can make the evaluation. You'll then be able to count all the checkmarks in the end

Use this formula in the helper column.

=IF(AND(MAX(COLLECT([Row ID]:[Row ID], Task:Task, Task@row)) = [Row ID]@row, COUNTIFS(Task:Task, Task@row) = COUNTIFS(Task:Task, Task@row, Status:Status, "Completed")), 1)

To make sure rows aren't being counted multiple times, the formula first looks to see if it is the max row (you could also use the created date if you had that in your sheet instead of [Row ID]), then it checks to see if the count of the specific activity equals the count of those activities that have status = Completed. If all of that is true, check the column.

You can either then sum these checkmarks in a formula or within your Report you can Summarize this column with Count.

Will this work for you?

Kelly

• ✭✭
Options

Thanks @Kelly Moore

Not sure if i input it correctly, doesn't seem to work.

=IF(AND(MAX(COLLECT([Entry #]:[Entry #], [Activity Description]:[Activity Description], [Activity Description]@row)) = [Entry #]@row, COUNTIFS([Activity Description]:[Activity Description], [Activity Description]@row) = COUNTIFS([Activity Description]:[Activity Description], [Activity Description]@row, Status:Status, "Completed")), 1)

• ✭✭
Options

It works!! I changed it to Modified date as per your recommendation. I believe previous issue was the prefix for the entry#. Thanks a lot @Kelly Moore

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!