Formula to identify number of parent rows with a specific status (excludes child rows)

APWURK
APWURK
edited 12/09/19 in Formulas and Functions

Hello, I'm quite new to smart sheets and am having trouble parsing out this particular formula I need to make for a work metrics sheet. 



Essentially our spreadsheet is comprised of many parent rows with associated childrens rows. The parent row has a column called "Status" which details where the account associated with the parent role is in our process. 



I would like a formula that helps count the amount of unique statuses in the parent rows. 



And just as an example, one of the unique statuses is named "Waiting For Docs" 



Any direction people could point me in would be immensely helpful and appreciate! 

Comments

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

    Hi,

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    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.

  • Hi Andree, 



    Thanks so much for your quick response! I'm unfortunately unable to remove all the identifying information in the document and share it with you so I screen shotted a sample of what I'm working with. 



    Essentially I need to create a formula that counts the status of each parent row, and ignores the children. So the formula would ideally sum up, for example, how many of the parent rows in the document say "Waiting For Docs" or any other status in our dropdown menu. 



    Thank you again for your help! And please let me know if there is more information you need. 

    -AP 

    SmartSheets Community Help Example.png

  • sean59916
    sean59916 ✭✭✭

    AP, 

    You can add a reference column to identify parent from children rows and then count based on that column and status. For example (see images):

    • Add a reference column with the formula =COUNT(ANCESTORS([Column1]@row))
    • I added a new sheet, (but you can use the same) and then summarized the parents using the formula                 =COUNTIFS({Test Sheet 1 Range 1}, [Primary Column]1, {Test Sheet 1 Range 2}, 0)

    Note, that the formula for the summary is dependent on how many parents you have above the client name.

     

    I hope this helps?

     

    Sean

    2019-05-03_9-29-24.jpg

    2019-05-03_9-32-16.jpg

    2019-05-03_9-34-29.jpg

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

    Happy to help!

    I saw that Sean answered already!

    Let me know if I can help with anything else!

    Best,

    Andrée

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To expand on Sean's solution...

     

    If you have multiple levels of hierarchy within the sheet, this can be accounted for using his solution but changing up the criteria slightly. You would just need to know which level(s) you are wanting to count.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!