COUNTIFS - Projects that equal a certain value and are CHILDREN

Daniel Barber

I looking to pull the number of projects under a certain package. The Package would be the PARENT row and the projects would be CHILDREN rows. Something like this below where each project also has the same package number (used for identification elsewhere)

I would like to be able to count the number of Projects/rows, if it is not a Parent row. I am pulling from the example above into another sheet and have matched the Package name so I could use the following formula:

Basically saying "If anything in the Package column from Sheet 1 (first image) matched the name 'Package 1' from sheet 2 (second image), then count it." This is obviously pulling the parent row as well, inflating the number of projects by one.

I know I need to use something like CHILDREN(…) but Im struggling to get it to work.

Any suggestions?

Thank you!

Daniel Barber

After some more research I found that you can't use the CHILDREN function when referencing another sheet.

So to make this work, I have added a Helper column with checkboxes for the Parent rows and used the following formula:

=COUNTIFS({Project Intake Sheet Range 5}, [Primary Column]14, {Project Intake Sheet Range 8}, =0)

Where Range 5 = the Package column from Sheet 1, [Primary Column] = the 'Package 1" name in Sheet 2, and Range 8 = the new checkbox column in Sheet 1 with the "= 0" indicating it is not checked.