Column formula for only Children rows with Parent row =SUM(CHILDREN())

Marina · 07/15/21

I have PARENT and CHILDREN rows with the column where for PARENT row I indicate "TOTAL" in separate column. I would like to create Column Formula to calculate "Estimated Total" either for PARENT raw or for CHILDREN row automatically.

Andrée Starå · 07/15/21

Hi @Marina

I hope you're well and safe!

Try something like this.

=IF(COUNT(CHILDREN(Task@row)) > 0, SUM(CHILDREN()), 
[Estimated Units]@row * [Price per Unit]@row)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Bassam Khalil · 07/15/21

Hi @Marina

Hope you are fine, please try the following:

1- add a helper column to define if the row is children or parent and use the following formula and convert it to column format formula:

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

2- in the Estimated Total column use the following formula and convert it to column format formula:

=IFERROR(IF(Level@row = 0, [Estimated Units]@row * [Price per Unit]@row, SUM(CHILDREN())), "")

the following screenshot shows the result

Andrée Starå · 07/15/21

Hi @Marina

I hope you're well and safe!

Try something like this.

=IF(COUNT(CHILDREN(Task@row)) > 0, SUM(CHILDREN()), 
[Estimated Units]@row * [Price per Unit]@row)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Marina · 07/16/21

@Andrée Starå It worked in test file but when I tried in Production, it didn't. Instead of "Task" column I have "Project Names [Primary]" column name. How would I use in the formula? I think I am missing correct punctuation. I need to calculate "Price per unit" and "Estimated hours" under children rows.

I even changed the name of the column to "Project Names" and used =IF(COUNT(CHILDREN([Project Names])@row)) > 0, SUM(CHILDREN()), [Estimated hours]@row * [Price per unit]@row)

but got an error #UNPARSEABLE

Marina · 07/16/21

@Andrée Starå actually I was able to figure out. You can ignore my comment above. This formula worked for me

=IF(COUNT(CHILDREN([Project Name \[Primary\]]@row)) > 0, SUM(CHILDREN()), [Estimated hours]@row * [Price per unit]@row)

Thank you for your suggestion.

Andrée Starå · 07/16/21

@Marina

Excellent!

You're more than welcome!

Sarah123 · 08/19/22

The formula works great for me, however, I'm trying to make it a column formula and for some reason it won't let me (even though I'm using @row within the formula and not specific cells). =IF(COUNT(CHILDREN([Header Row]@row)) > 0, SUM(CHILDREN()), 40 - [FY22 W46 RH]@row - SUMIF({Person}, $Resources@row, {FY22 W46}))

Genevieve P. · 08/19/22

https://community.smartsheet.com/discussion/comment/340388#Comment_340388

Hi @Sarah123

There's an absolute reference in this formula (a $ sign locking in a specific column and/or row).

In your case, it's before the "Resources" column:

=IF(COUNT(CHILDREN([Header Row]@row)) > 0, SUM(CHILDREN()), 40 - [FY22 W46 RH]@row - SUMIF({Person}, $Resources@row, {FY22 W46}))

Remove that out and you should be good to go!

=IF(COUNT(CHILDREN([Header Row]@row)) > 0, SUM(CHILDREN()), 40 - [FY22 W46 RH]@row - SUMIF({Person}, Resources@row, {FY22 W46}))

Cheers,

Genevieve

Sarah123 · 08/19/22

Thank you! @Genevieve P. Didn't even realize that piece.

Column formula for only Children rows with Parent row =SUM(CHILDREN())

Best Answer

Answers

Help Article Resources

Categories