Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Count Children if Criteria is Met

Ray Rios
Ray Rios
edited 12/09/19 in Archived 2015 Posts

I'm trying to get the =COUNT(Children()) formula to work with a criteria. For instance, I have children that require a status of 'In Progress' or 'Completed', when the status is 'Completed', I want the Count to reflect the number still 'In Progress' as opposed to ALL the items listed below. is it possible?

Comments

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    No enhancement needed...

     

     

    1) Add two hidden column

    2) [HiddenColumnA]2 :  =If([Status]2 = "Completed", 1, 0)

    2) [HiddenColumnB]2  :  =If([Status]2 = "In Progress", 1, 0)

    3)in the visible column tallying items to get the number complete :  =If([Status]1 = "Completed",  Sum(CHILDREN([HiddenColumnA]1)) ,  sum(CHILDREN([HiddenColumnB]1)) )

    4)in the status column of the parent row  =IF(COUNT(CHILDREN(Status1)) > SUM(CHILDREN(HiddenColumnA1)), "In Progress", "Completed")

     

     

    That should get you there.

    -Brett

    Capture.JPG

  • JamesR
    JamesR ✭✭✭✭✭✭

    Ray,

     

    Countif() should work for you.

     

    =COUNTIF(CHILDREN(), "In Progress")

    James

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    Much simpler Jay.  It eliminates the need for hidden columns.

     

  • Ray Rios
    Ray Rios
    edited 10/06/15

    I've tried both options and neither of them do what I need. In the Parent Column, I currently have a formual set like, ="Departmental Task (" + COUNT(CHILDREN()) + ")" This gives the result +Departmental Task (#). The "#" reflected are all the "Children" under that parent row regardless of the status in another column. What I'm trying to accomplish is the "#" to be a count only if the "Children" below have a status in another column not equal to "complete."

    SmartSheet.PNG

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    Ray,

    Make a copy of your sheet and , scrub and senstive company secrets from it and publish the web version.  That way we can see the all formulas and find the bugs.

     

     

    -Brett

  • Travis
    Travis Employee
    edited 10/06/15

    Hi Ray,

     

    This formula will subtract the number of times "Completed" is shown from the number of children. It will be useful if you have more than two status options.

     

    ="Departmental Task (" + (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "Completed")) + ")"

     

    If this is located in your Task column, leave the first CHILDREN() alone, but add a cell reference to the second CHILDREN(). For example if your parent row is row 1:

     

    ="Departmental Task (" + (COUNT(CHILDREN()) - COUNTIF(CHILDREN(Status1), "Completed")) + ")"

     

     If you only have two possible status options, use this:

     

    ="Departmental Task (" + COUNTIF(CHILDREN(), "In Progress") + ")"

     

    Again, you will need to referance the Status column parent cell:

     

    ="Departmental Task (" + COUNTIF(CHILDREN(Status1), "In Progress") + ")"

This discussion has been closed.