Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Count Hierarchies without including dupes

✭✭✭✭✭✭
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.


Any assistance is greatly appreciated.

Best Answer

  • ✭✭✭✭✭✭
    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!

Trending in Formulas and Functions