CountIFS for Parent Row Only


Hello - 

I am displaying some information regarding things that are approved, in queue or denied. But I am needing to only count parent rows and not the children for them.  So the data collected are just for the overall brand and not sub-brands. Is there a way for me to make this happen?

Thank you - 




  • Frank Falco
    Frank Falco ✭✭✭✭✭✭

    You will need to add some columns that you can hide to calculate which rows are a parent.

    If you only want to absolute top-level parent, you can use

    Level: =COUNT(ANCESTORS()+ 1

    When Level = 1 it is the top-level, 2, 3, and so on for sub levels

    If you want to know about sub parents

    Subs: =COUNT(CHILDREN()) 

    any row that has Subs>0 is also a parent

    Using these two you can get a good knowledge of where you are in the hierarchy.


    When you have this in place you can then use the level or subs values in your COUNTIFS formulas. 

    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Mmurray,

    I use this one all the time in my client solutions. I name the column Level and use that to simplify conditional formatting and more.

    Try something like this.

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

    The same version but with the below changes for your and others convenience.

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

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

    I hope that helps!

    Have a fantastic week!


    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD



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

    W: | | P: +46 (0) - 72 - 510 99 35

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

  • NikkiOno
    NikkiOno ✭✭✭✭

    @Andrée Starå this counts the parent rows and give them a 1 but how do i get a 2, 3 for children and grandchildren rows?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!