Exclude Rollup / Summary Tasks from Summary Sheet

Options

Hi All,

My Smartsheet has Rollup / Summary Tasks, Deliverables/Tasks and Milestones. On the Summary Sheet, I only want to count the actual Deliverables/Tasks i.e. not the Rollup/Summary tasks or milestones.

I found how to add a column that determines whether a row is a child or a parent using -

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, 1)

And I've managed to add a formula to my Summary sheet that reads -

=COUNTIFS(Workstream:Workstream, "Supplier Engagement", Status:Status, "Yet to Start", Child:Child, 1, Duration:Duration, "<>0")

However, I dont really want the Child column in my sheets and would prefer to have the formula in the Summary sheet (so replacing 'Child:Child, 1')

Any guidance on how to combine these?

Thank you

Tags:

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @GTN

    You've done exactly what I would do! Hierarchy is specific to each sheet, so adding a helper column to identify the hierarchy and then using that helper column in your cross-sheet formula is the solution for this type of formula.

    If you don't want the helper Child column showing, you can Hide it in the source sheet but it will still work fine in the formula.

    Cheers,

    Genevieve

  • GTN
    GTN ✭✭
    Answer ✓
    Options

    Hi Genevieve

    Perfect thank you - Child column is now hidden

    Thank you

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @GTN

    You've done exactly what I would do! Hierarchy is specific to each sheet, so adding a helper column to identify the hierarchy and then using that helper column in your cross-sheet formula is the solution for this type of formula.

    If you don't want the helper Child column showing, you can Hide it in the source sheet but it will still work fine in the formula.

    Cheers,

    Genevieve

  • GTN
    GTN ✭✭
    Answer ✓
    Options

    Hi Genevieve

    Perfect thank you - Child column is now hidden

    Thank you

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!