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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

  • Kelvin
    Kelvin ✭✭
    edited 05/04/23
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    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

  • Kelvin
    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)


  • Kelvin
    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!