Count Hierarchies without including dupes

jgneely72151jgneely72151 ✭✭✭✭✭
edited 01/31/21 in Formulas and Functions
01/31/21 Edited 01/31/21
Accepted

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

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    Accepted 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([email protected])) > 0, COUNT(ANCESTORS()) + 1)

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

Answers

  • jgneely72151jgneely72151 ✭✭✭✭✭

    My apologies! I was hurrying when I created this data and just realized that I should be using the distinct function in my count formula. When I tested it, I saw my error in how i listed the names on the hierarchy. That was giving me an incorrect count. I corrected it.

    Anyhow, now that I know that I can use =count(distinct(Placement ID]:[Placement ID] to return a count for me, I still need it to count the number of cohorts and number of students using the Placement ID column and I keep getting unparsable error. Again, so sorry for the error earlier but any help you can provide is most appreciated!



  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    Accepted 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([email protected])) > 0, COUNT(ANCESTORS()) + 1)

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I would suggest just a text/number column with

    =COUNt(ANCESTORS())

    in every row.


    You can then use

    =COUNTIFS([Helper Column]:[Helper Column], 0)

    to count the cohorts and then change the 0 to a 1 to count the students.

  • jgneely72151jgneely72151 ✭✭✭✭✭

    @Bassam.M Khalil OMG!!! Last night before going to bed, I found this solution on YouTube and was going to try it out this morning. I was thinking this will have to be the fix to my issue and you confirmed it this morning. I really, really, really appreciate you!!!! Thank you so much!

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    edited 02/02/21

    Happy to help you anytime @jgneely72151 also check the great advice by @Paul Newcome

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

Sign In or Register to comment.