Using CHILDREN(cell address) as a range in SUMIFS
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 minidashboard 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))
Comments

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.

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!