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 an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful". 

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 an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful". 

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

    thinkspi.com

  • 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 an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful". 

Sign In or Register to comment.