Column formula for only Children rows with Parent row =SUM(CHILDREN())
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.
Best Answer

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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers

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
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

@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

@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.

SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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}))

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

Thank you! @Genevieve P. Didn't even realize that piece.
Help Article Resources
Categories
Check out the Formula Handbook template!