Sums, IF statements and parent rows -- combined in one formula
I'm struggling to figure out how to combine two formulas.
Context: my team doesn't like to think in hours, just general size of a task (S, M, L,) so I've created a helper column where each value relates to a number (XS = 1, S = 2, M = 4, L = 8, XL = 16), and an IF statement to return the values. This formula works fine in the [Estimated Time] column.
=IF([Task Size]5 = "XS", 1, IF([Task Size]5 = "S", 2, IF([Task Size]5 = "M", 4, IF([Task Size]5 = "L", 8, IF([Task Size]5 = "XL", 16, "")))))
So far so good.
But now I want to add that IF this is a parent row, sum the values of its' children, otherwise, use that formula above. This is where I've landed but it's giving me an #UNPARSEABLE error:
=IF(COUNT(CHILDREN([Task / Request])1) > 0, (SUM(CHILDREN())", IF([Task Size]1 = "XS", 1, IF([Task Size]1 = "S", 2, IF([Task Size]1 = "M", 4, IF([Task Size]1 = "L", 8, IF([Task Size]1 = "XL", 16, "")))))
Could really use some help here. Maybe I missed a bracket, or maybe I'm over my head. Pretty sure trying to use that sum(children()) is messing with me but who knows. Thanks!
Comments
-
Your parenthesis are a little off, and you have an extra " tucked in after SUM(CHILDREN()).
Give this a try...
=IF(COUNT(CHILDREN([Task / Request]1)) > 0, SUM(CHILDREN()), IF([Task Size]1 = "XS", 1, IF([Task Size]1 = "S", 2, IF([Task Size]1 = "M", 4, IF([Task Size]1 = "L", 8, IF([Task Size]1 = "XL", 16, ""))))))
-
Eek! That's perfect! Thanks so much for the quick reply!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!