Is there a way to count the number of parent rows with more than a certain number of children?

I have a column of P/N's in development. Most are individual projects, but some are for customer that have multiple P/N's which are indented under a parent row. I am wanting to know if there is a way to count how many projects I have that contain more than 25 P/N's or "children".

Best Answer

  • Heather Duff
    Heather Duff âś­âś­âś­âś­âś­âś­
    Answer âś“

    Max,


    I think you could do this! You could add a new column, and use this as the column formula, assuming your column with the parents/children is called "Project Name":

    =count(children([project name]@row))

    Let's say I call the column I just created "Count" (for lack of a better idea).

    Then you could put this formula somewhere - maybe in a Sheet Summary:

    =countif(count:count, >25)

    Hope this helps!


    Best,

    Heather

Answers

  • Kelly Moore
    Kelly Moore âś­âś­âś­âś­âś­âś­

    Hello @Max Corbin

    Yes. The easiest way is with a helper column. I will show you two ways, depending upon what you ultimately wish to do with the data

    With a checkbox column you can easily flag the parents with children >25. If you later wanted to know how many were >30, you would have to change the formula and/or add another helper column.

    =IF(COUNT(CHILDREN([your primary row@row))>25, 1)

    If you were using this in a report and you wanted to be able to easily adjust the number, use a Text/Number column and count. Since I don't like to see a bunch of zeros in my sheets, I first checked to see if the row was a parent row.

    =IF(COUNT(CHILDREN([your primary row]@row)) > 0, COUNT(CHILDREN([your primary row]@row)))

    Be sure to add your primary column name to the formula.

    Kelly

  • Heather Duff
    Heather Duff âś­âś­âś­âś­âś­âś­
    Answer âś“

    Max,


    I think you could do this! You could add a new column, and use this as the column formula, assuming your column with the parents/children is called "Project Name":

    =count(children([project name]@row))

    Let's say I call the column I just created "Count" (for lack of a better idea).

    Then you could put this formula somewhere - maybe in a Sheet Summary:

    =countif(count:count, >25)

    Hope this helps!


    Best,

    Heather

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!