COUNTIF with Parent rows only, exclude children
I want to create a COUNTIF function that will count only the Parent row of a status column in a project tracking sheet. This is to create a metric sheet that will show all project statuses by each project manager's sheet. There is a status column using Green/Yellow/Red/Blue identifiers, and children rows need to include the status column GYRB. For the metric sheet, I need all children rows to be excluded.
The metric sheet is so I can track all parent projects without the children rows inflating the data.
Comments
-
You can create a helper column (called "Helper" for this example). You can then use a basic COUNT function
=COUNT(CHILDREN([Task Column Name]@row))
You can then pull all rows that do NOT have a zero in the Helper column meaning that the row has at least one child row (making it a parent row).
You could also make the Helper column a checkbox type and use
=IF(COUNT(CHILDREN([Task Column Name]@row)) > 0, 1)
That would check the box for all parent rows which makes pulling all parent rows that much easier.
-
Paul,
Thanks for the help. Would this function work if a row has no children? For example, I have 80 rows, only 50 of which have children rows associated. The purpose of this is so the "main project line" is being measured in the overall metric for counting the projects by project manager. My data is skewed right now because children rows are being included in the count for rows with a Status of "Red, Green, Yellow, Blue" when they should actually be excluded.
Nevermind! I actually solved the probably by fixing the sheet's filter, which is the cause of all of my extra "blue" statuses.
-
This will work for rows without Children rows. The purpose behind counting the children rows is to establish what is a parent row (anything that has a child row under it). Basically the formula for the checkbox column type will check the box if there is a child row under it. If there are no child rows underneath (meaning not a parent row), the box will remain blank.
I am glad you were able to figure out a solution though.
-
@Paul Newcome Hope all is well. I just stumbled upon this, and i need some help. I do see you mentioned that Rows that dont have Children Rows will also have some sort of indicator, and with your above formula i am not getting the same results.
It Shows me a checkbox for the rows that have child rows, but not regular rows.
What i am trying to accomplish is to get an accurate count of how many rows i have Excluding the child rows.
Thanks for always being here for the SS community.
-
@DaniAmi Are you able to provide a screenshot for reference?
-
Hi @Paul Newcome! I am having the same issue as DaniAmi, where if the row has no children, the box is not checked and not counted, is there a way to count the rows without children as well?
-
@Angelo DeMaio I'm not sure I follow. If you want to only check the rows that do not have child rows, then you would not get any parent rows checked, but if you want to check both parent rows and rows that have no child rows then you would end up checking every row. Can you be more specific as to what exactly should and should not be checked?
-
Hi @Paul Newcome! I think i found a work around by adding a child to the rows i needed to count. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!
