Formula calculating Status on Parent and Child Rows

Laurenzella

Right now, the overall project status formula displays as "Complete" only if all the checklist items are marked as "Completed". We have been asked to update the formula so that it if all checklist items are marked "Completed" OR "Cancelled" the overall project status will be "Complete". The "Tasks Not Needed" references a hidden column that tells certain tasks not to be included because they are checked off - this is accomplished with the Default filter.

Current formula:

=IF([Project Status Override]# <> "", [Project Status Override]#, IF(AND(COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, "Completed") = COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Complete", IF(AND(COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, "Not Started") = COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Planning", "Active")))

Screenshot:

Kelly Moore

Hey @Laurenzella

Will this work for you?

=IF([Project Status Override]# <> "", [Project Status Override]#, IF(AND(COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, OR(@cell = "Completed", @cell = "Cancelled")) = COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Complete", IF(AND(COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, "Not Started") = COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Planning", "Active")))

Kelly

Kelly Moore

Hey @Laurenzella

Will this work for you?

=IF([Project Status Override]# <> "", [Project Status Override]#, IF(AND(COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, OR(@cell = "Completed", @cell = "Cancelled")) = COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Complete", IF(AND(COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, "Not Started") = COUNTIFS([Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Planning", "Active")))

Kelly

jmyzk_cloudsmart_jp

Hi @Laurenzella

I added two helper columns, Parent and Relevant, to shorten the formula and avoid repetition and human error when inputting parent status.

[Parent] =IF(COUNT(CHILDREN()) > 0, 1)
[Relevant] =IF(OR(Summary@row, [Tasks Not Needed]@row, Parent@row, Hierarchy@row = 0), 0, 1)
[Relevant 2] =IF(OR(Summary@row, [Tasks Not Needed]@row, Parent@row), 0, 1)

In your formula, the COUNTIF condition, "[Tasks Not Needed]:[Tasks Not Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0" is repeated several times, so I replaced it with "Relevant@row, 1".

Also, your scheme does not automatically set the parent row's Status values. Instead, it assumes human input, which may lead to a wrong interpretation of the parent status or the value not being input.

So, I added the [Parent] column to make an Action Items relevant when [Parent] is unchecked. (Note: In this case, you can remove the Hierarchy condition, as shown in the [Relevant 2] formula.)

The whole formula is as follows;

[Notes]
=IF(
    ISBLANK([Project Status Override]#), 
    IF(
        OR(
            COUNTIFS(Relevant:Relevant, 1, Status:Status, "Completed") = COUNTIF(Relevant:Relevant, 1), 
            COUNTIFS(Relevant:Relevant, 1, Status:Status, "Cancelled") = COUNTIF(Relevant:Relevant, 1)
        ), 
        "Complete", 
        IF(
            COUNTIFS(Relevant:Relevant, 1, Status:Status, "Not Started") = COUNTIF(Relevant:Relevant, 1), 
            "Planning", 
            "Active"
        )
    ), 
    [Project Status Override]#
)

You can also put Sheet Summary fields, as shown in the image below, to simplify the formula.

=IF(
    ISBLANK([Project Status Override]#), 
    IF(
        OR(
            [Relevant Sub-tasks: Complete]# = [Relevant Sub-tasks]#, 
            [Relevant Sub-tasks: Cancelled]# = [Relevant Sub-tasks]#
        ), 
        "Completed", 
        IF(
            [Relevant Sub-tasks]# = [Relevant Sub-tasks: Not Started]#, 
            "Planning", 
            "Active"
        )
    ), 
    [Project Status Override]#
)

https://app.smartsheet.com/b/publish?EQBCT=d10f69cc30154d83b63e31716a12ad74 (Link to the editable published demo sheet. You can test the formula by changing the Status values.)

If you do not want to add helper columns, the formula would be as follows: (Note: To make this formula work, you must manually check the [Task Not Needed] checkbox in the parent rows. To prevent human errors, you can substitute the Hierarchy condition with Parent condition.)

[Note 2]
=IF(
    [Project Status Override]# <> "", 
    [Project Status Override]#, 
    IF(
        OR(
            COUNTIFS(
                [Tasks Not Needed]:[Tasks Not Needed], 0, 
                Summary:Summary, 0, 
                Hierarchy:Hierarchy, >0, 
                Status:Status, "Completed"
            ) = COUNTIFS(
                [Tasks Not Needed]:[Tasks Not Needed], 0, 
                Summary:Summary, 0, 
                Hierarchy:Hierarchy, >0
            ),
            COUNTIFS(
                [Tasks Not Needed]:[Tasks Not Needed], 0, 
                Summary:Summary, 0, 
                Hierarchy:Hierarchy, >0, 
                Status:Status, "Cancelled"
            ) = COUNTIFS(
                [Tasks Not Needed]:[Tasks Not Needed], 0, 
                Summary:Summary, 0, 
                Hierarchy:Hierarchy, >0
            )
        ), 
        "Complete", 
        IF(
            AND(
                COUNTIFS(
                    [Tasks Not Needed]:[Tasks Not Needed], 0, 
                    Summary:Summary, 0, 
                    Hierarchy:Hierarchy, >0, 
                    Status:Status, "Not Started"
                ) = COUNTIFS(
                    [Tasks Not Needed]:[Tasks Not Needed], 0, 
                    Summary:Summary, 0, 
                    Hierarchy:Hierarchy, >0
                )
            ), 
            "Planning", 
            "Active"
        )
    )
)