Countifs - with Hierarchy (Grandchild)

cedwards
cedwards
edited 12/09/19 in Formulas and Functions

I am trying to write a formula to count the lowest level of "Completed" items.  I have multiple Projects and different Status all on the same sheet.

 

 

=COUNTIFS((CHILDREN[Variable Features/Tasks]:[Variable Features/Tasks]), Status:Status, "Complete", Project:Project, "Test")

 

I'm not working in excel - I just wrote up the example there.

 

Hierarchy.JPG

Comments

  • Hello,

    CHILDREN can't accept a range of an entire column. You'll want to either leave it blank (if the function is in a parent and you want to reference that parent's children) or reference a single cell (if the function isn't in a parent whose children you want to reference). More info on this function can be found in the help center: https://help.smartsheet.com/function/children

    You'll want to use a formula like this: 

    =COUNTIFS(CHILDREN(), Status:Status, "Complete", Project:Project, "Test")

    Or a function like this:

    =COUNTIFS(CHILDREN([Variable Features/Tasks]14), Status:Status, "Complete", Project:Project, "Test")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!