Counting children status

Emily T.

Hi!

I'm trying to count the number of children that are "Completed" using the CHILDREN formula and still cannot get it to work after searching and reviewing in the Smartsheet Community.

In a separate smartsheet, I've attempted various formulas without luck. Below makes the most sense to me out of the various formulas attempted but as it's not correct, any advice would be much appreciated!

=COUNTIF(CHILDREN({Integration Range 5}1) ="Completed")

The range mentioned is the Parent cell of the Pre-Close in the attached pic.

Thank you!

Screen Shot 2021-12-15 at 1.59.21 PM.png

Genevieve P.

Hi @Emily T.

Hierarchy is specific to each sheet and currently the CHILDREN function needs to reference data in the current sheet and cannot be used in a cross-sheet formula.

The way I would get the information you're looking for is to add a helper column in the source sheet. I would suggest using this:

=PARENT(Task@row)

This will populate the Parent name of the Task column to all the Child rows. Now you can use this Helper Column (lets call it "Parent Name") as your criteria in the COUNTIFS function!

For example:

=COUNTIFS({Parent Name Column}, "Pre-Close", {Status Column}, "Completed")

Does that make sense?

If you're looking for all the Child Rows versus just the child rows of the Pre-Close parent, we could add a different type of helper column in your source sheet to indicate the hierarchy. Let me know if you need help with this and I'd be happy to explain further.

Cheers,

Genevieve