Using CHILDREN(cell address) as a range in SUMIFS

Ben Donahue

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

L_123

Can you post a picture of your smartsheet? it would be easier to understand.

 

That said I can already tell you the issue is your ranges. In all of the math functions smartsheet has, if there are multiple ranges, they MUST contain the same number of values. You have a full column reference and a children reference inside of a sumifs, the program will never accept that.

or can only handle one value at a time, you cannot put a range into it. instead you use the "@cell" reference.

 

Sumif(A1:A100,or(@cell < 1, @cell > 50)) 

Paul Newcome

The easiest way I have found thus far to account for a specific hierarchy level is to use a helper column to generate a number based on such.

 

=COUNT(ANCESTORS([Column Name]@row))

 

and then refer to this column in your formulas.