Count Children if Criteria is Met

Ray Rios

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?

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

JamesR

Ray,

 

Countif() should work for you.

 

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

James

Brett Evans

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

 

Ray Rios

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."

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

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