Count Hierarchies without including dupes

jgneely72151
jgneely72151 ✭✭✭✭✭✭
edited 01/31/21 in Formulas and Functions

Is it possible to count the number of the hierarchies?

Right now, I'm using the count function and manually clicking on level 1 for those counts and level 2 for those counts. My document is rather large and I don't want to click through each one and someone may insert a new level and I don't want a break in my formula.

The formula I used for level 2 that counts the total number of students is as follows:

=COUNT([Placement ID]2, [Placement ID]5, [Placement ID]8, [Placement ID]10, [Placement ID]13)

I didn't use the count column because I don't want to count everything there since there are dupes and I need a true count of the students. Each student can have 1-3 assignments which is why you see the dupes. I just want to count the level which will give me 5 students and not 15.

I hope my example makes sense.

The info is fictitious but my sheet has the same layout and I need to be able to count the cohorts and individual students, not dupes.

image.png


Any assistance is greatly appreciated.

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer βœ“

    Hi @jgneely72151

    Hope you are fine, you can create a helper column call it "levels" and use the following formula to define each level of the Hierarchies then you can count how many of level 1 for example, and how many of level to ...atc

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

    PMP Certified

    bassam.khalil2009@gmail.com

    β˜‘οΈ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!