Formula for only parent rows

Options

I created a helper column to sum all the children rows under a parent row. I want to now create a column formula that only applies to the parent rows, that populates whatever is in the child row below the parent row into the parent row. I have it working if I apply the formula to each parent row but can't get it to work as a column function.

Here is the formula that I am using (copying and pasting into each parent row)

=INDEX(CHILDREN(), MATCH(MIN(COLLECT(CHILDREN([Row ID]@row), CHILDREN([Row ID]@row), >[Row ID]@row)), 0))

Tags:

• Moderator
Options

To accomplish what you intend you would need the formula to discriminate between parent and children rows. A possible suggestion would be to add the following statement to your formula: =IF(COUNT(CHILDREN([Primary Column]@row)) >= 1, Value if True (your formula), "")

Note that the "value if false" argument in here is a blank value but you can use this space if you wish to perform a different calculation in children rows. Although I haven't tested this in my own environment the whole collated formula along with yours could look like the following:

=IF(COUNT(CHILDREN([Primary Column]@row)) >= 1, INDEX(CHILDREN(), MATCH(MIN(COLLECT(CHILDREN([Row ID]@row), CHILDREN([Row ID]@row), >[Row ID]@row)), 0)), "")

Feel free to make adjustments as needed if this returns an error or doesn't fully fit your sheet structure.

I hope that this can be of help.

Cheers!

Julio

• Options

Thank you so much, I appreciate your help! Unfortunately, it still giving me an error.

• Moderator
Options

If you wish, you may share a screenshot of your sheet with the error returned and I'll be happy to have a look at it. Please make sure to hide any sensitive information in there.

Cheers!

Julio

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!