Hierarchy Formula

FrankOrtz
FrankOrtz
edited 12/09/19 in Smartsheet Basics

Hello!

I've been trying to count how many milestones called "Testing" with %Complete = 100% are per project. Each milestone is a child of a parent called "Milestone" and this is at the same time a child of the project name. At the end, each project is a child of a big project name. (I've attached an example of the structure). 

I have read others comments and documentation of the hierarchical formulas, however, it's not clear to me how to develop it correctly. 

If someone can help me to elaborate it, I'll appreciate it a lot. I'm learning in the process. 

 

Thank you!

 

 

 

 

 

projects stucture.JPG

Comments

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

    Hi,

    Try something like this.

    I've used CHILDREN in my example, but you could change the ranges if you need.

    =COUNTIFS(CHILDREN([% Complete]1); "1"; CHILDREN([Big Project Name]1); FIND("testing"; LOWER(@cell)) > 0)

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

    =COUNTIFS(CHILDREN([% Complete]1), "1", CHILDREN([Big Project Name]1), FIND("testing", LOWER(@cell)) > 0)

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

    Did it work?

    Have a fantastic weekend!

    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: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Andree:

    A few observations if I may...

    .

    I feel like using the CHILDREN function may actually keep this from working as it would only be looking at the child rows of the specified cell. You formula above would only be looking at the rows containing the project numbers. 

     

    From what I gather from the screenshot Frank provided, only the lowest level of hierarchy is going to contain the word "Testing", so couldn't we reference the entire column instead of having to look at a specific hierarchy level?

    .

    I love the LOWER addition. I have been building that into my FIND functions more and more lately. It just makes life easier.

    .

    And finally... There you go with those quotes again. Lol! cheekywink

    Using "1" as the criteria will mean that it is looking for that very specific text. Using 1 means it will look for that number.

    .

    Taking the above into account and using your formula as a base, I would actually end up making the suggestion of...

     

    =COUNTIFS([% Complete]:[% Complete], 1, [milestones names]:[milestones names], FIND("testing", LOWER(@cell)) > 0)

    thinkspi.com

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

    Paul,

    You may not but you already did so! laugh

    I don't agree regarding the children because Frank wanted to have them per project and it seems like it should work according to his structure.

    Frank decides wink 

    The quotes don't matter in this scenario because it will work anyway. I think I added them because indecisionwinklaugh

    Best,

    Andrée

     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Andree,

    I missed the "per project" when I first read the post. I was going for an overall tally. That's my mistake.

    So you would put the formula you provided on the rows that have "Milestones project #" and replace the cell reference to "[milestones names]@row"?

     

    As for the quotes... I can never figure out when they can and can't be used. I've always just used the general rule of thumb that quotes means text and no quotes means number. I didn't realize it would work in a % Complete column like that. Apparently you can even manually enter "100%" without having it formatted as a percentage, and it will still register as "1".

    Thanks for putting me in my place. blushwinklaugh

    .

    .

    Frank,

    Nevermind our back and forth. Hopefully you're able to sift through all of the mess and find a working solution. Let us know if you can't and we will see if Andree can come up with something else. Hahaha. Welcome to the Community. winkyes

    thinkspi.com

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

    Paul,

    No worries!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hello Gentlemen! 

    Sorry for my late answer.

    Thank you for your help! It was very helpful. At the end, I solved what I wanted with two different formulas. One formula counts if a particular milestone has achieved 100%, and the other formula is when the milestone has been started (>0%) but it's not completed yet. 

    Formula 1= IF(COUNTIFS(CHILDREN([% Complete]1), 1, CHILDREN([Interface Name]1), OR(FIND("Coding Program", @cell) > 0, FIND("Modify", @cell) > 0)) > 0, 1)

    Formula 2= IF(OR((COUNTIFS(CHILDREN([% Complete]1), <1, CHILDREN([Interface Name]1), OR(FIND("Coding Program", @cell) > 0, FIND("Modify", @cell) > 0, FIND("Development", @cell) > 0)) < SUM(COUNTIFS(CHILDREN([Interface Name]1), OR(FIND("Coding Program", @cell) > 0, FIND("Modify", @cell) > 0, FIND("Development", @cell) > 0)))), COUNTIFS(CHILDREN([% Complete]1), <1, CHILDREN([% Complete]1), >0, CHILDREN([Interface Name]1), OR(FIND("Coding Program", @cell) > 0, FIND("Modify", @cell) > 0, FIND("Development", @cell) > 0))), 1)

    I tried here and in other sheets to use the LOWER(@cell) functionality but I didn't get what I was expecting. The idea is to search in all the cells of the referenced column without differentiate between uppercase and lowercase words, right? For example, I want to search for the status project : "Complete", and this should be equal to "COMPLETE". Because it didn't work properly, I created this= ...OR(FIND("Complete", @cell) > 0, FIND("COMPLETE", @cell) > 0)

    Thank you so much again and have an amazing day!

     

     

     

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Glad you were able to find a working solution. In your particular examples above you would use something like this for the LOWER function...

     

    FIND("complete", LOWER(@cell)) > 0

     

    What you are doing is converting the contents of each individual cell to lower case then using it in the FIND function within the rest of your formula. Just be sure that the text you use to specify what you are looking for is in all lower case as well.

    thinkspi.com

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

    No worries!

    Happy to help!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Thank you! I'll use it soon and certainly it will work well! :)

     

  • I realize that this is an old thread, but I had a similar question that I think I figured out a novel approach. I wanted the level of the hierarchy, i.e. 0=Top, 1=Child, 2=Grandchild, etc. You can then use that to figure out the rest of your issue.

    I ended up using this formula:

    =IF(COUNT(PARENT()) = 0, 0, PARENT() + 1)


    If you wanted to start your count at 1 (i.e., 1= Top, 2= Child, 3= Grandchild, etc...

    =IF(COUNT(PARENT()) = 0, 1, PARENT() + 1)

    Hope this helps someone!

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

    Hi @Matthew Walkup

    Thanks for sharing!

    I usually use this one.

    =IF(COUNT(CHILDREN([email protected])) > 0; COUNT(ANCESTORS()) + 1)
    

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or 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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.