Using CHILDREN(cell address) as a range in SUMIFS

Ben Donahue
Ben Donahue ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

I am trying to find the total of Task Total Hours only for rows with a tier level of 2 and where % complete = 1. I am writing this in a summary cell in a mini-dashboard that requires me to write the formula in the % Complete column. I am trying to use CHILDREN(cell address) as a range. What I have read leads me to believe that this is not a problem. I trying to access this functionality, and I am falling short of the prize. Here is my formula:

=SUMIFS([Task Total Hours]:[Task Total Hours], [Tier Level]:[Tier Level], =1, CHILDREN([% Complete]22), =1)

[% Complete]22 is on the parent row, tier level 1.

I think I'm right. The computer disagrees. The computer is always right, and I am always wrong. 

Your help in this is greatly appreciated, thank you.

Follow up question: How do I turn this into an OR type logic, where  [Tier Level]:[Tier Level], =1 OR  [Tier Level]:[Tier Level], = 0

Would that be:

OR(([Tier Level]:[Tier Level], =1),([Tier Level]:[Tier Level], =0)) 


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!