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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thank you! @Genevieve P. Didn't even realize that piece.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!