Count completed task if all sub-task is completed

Kelvin

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.

Kelly Moore

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

Kelvin

Hi @Kelly Moore

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

Kelly Moore

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

Kelvin

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)

Kelvin

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