Sums, IF statements and parent rows -- combined in one formula

mrsbrackett

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! 

Paul Newcome

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, ""))))))

mrsbrackett

Eek! That's perfect! Thanks so much for the quick reply! 

Paul Newcome

Happy to help!