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

Options
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 ✭✭✭✭✭✭
    Options

    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 ✭✭✭✭✭✭
    Options

    Ray,

     

    Countif() should work for you.

     

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

    James

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭
    Options

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

     

  • Ray Rios
    Ray Rios
    edited 10/06/15
    Options

    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 ✭✭✭✭✭✭
    Options

    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
    Options

    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.