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

Max Corbin

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

Heather Duff

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

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

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