Excluding child rows from counts in formulas in a sheet

Options

I have inherited a project with a metrics sheet that is the source for charts in a Dashboard. The metircs sheet is using formulas to CountIf cretain criteria are met, such as Status, Priority, Owner, etc. I would like these countif formulas to only be counting the Parent rows and not include the child (indented) rows. For example, the current formula to count the number of Rows with a Status of "Backlog" is this" =COUNTIFS({People Team Initiatives Range 3}, "Backlog", {People Team Initiatives Range 1}, "<>") but this counts every row with this status. How can I only count the parent rows and exclude child rows? Thanks!

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Janet Krieger

    I hope you're well and safe!

    You'd need to add a so-called helper column to the source sheet.

    Something like.

    I usually add a so-called helper column called Level with the formula below, and then you can use it to identify parents and use it for conditional formatting and more.

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

    You can then use that in the formula to determine if it's a parent or not.

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic day!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!