Count CHILDREN() and all children of children

J.Barrow

So I am working on a formula to help me determine the health of a project based on the RYG balls and I want the project to count not only the children but all of the RGB balls of the children.

So currently I am working on the formula for the top gray box.

The formula I am currently using is the following: =IF(((COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")) / (COUNT(CHILDREN()) * 2) <= 0.49, "Red", IF(((COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")) / (COUNT(CHILDREN()) * 2) <= 0.74, "Yellow", "Green"))

So I want the gray box to count all 10 "balls" and it currently is counting only 4 balls. So I want a way to tell it to count both the children and the children's children if possible. as you can see the current status is green and I feel like it should be red if it counted all 10 vs only the 4 children.

I hope this amazingly smart community can help me!

J.Barrow

@Mike TV,

Thanks for your help. I just figured it out. You need to use the formula for DECENDANTS not CHILDREN.

So the formula was: =IF(((COUNTIFS(DESCENDANTS(), "Green") * 2) + COUNTIFS(DESCENDANTS(), "Yellow")) / (COUNT(DESCENDANTS()) * 2) <= 0.49, "Red", IF(((COUNTIFS(DESCENDANTS(), "Green") * 2) + COUNTIFS(DESCENDANTS(), "Yellow")) / (COUNT(DESCENDANTS()) * 2) <= 0.74, "Yellow", "Green"))

Mike TV

@J.Barrow

Your overall formula defaults to Green when nothing else is true. I think what is happening is you've failed to tell the formula which column to look for children in. So it's looking in it's own column. In each instance of where you have CHILDREN() I believe you just need to put CHILDREN([Column Name]:[Column Name]). I believe that should fix it for you.

J.Barrow

@Mike TV,

No that is not the problem. CHILDREN() sets it to the Colum and adding CHILDREN([Column Name]:[Column Name]) does not change the issue as CHILDREN only does the items indented not the indented of the indent. or CHILDREN OF CHILDREN. I also thought about just getting rid of the CHILDREN and just doing ([Column Name]:[Column Name]), but that issue is that I am building a template where they will be adding and deleting columns and that will cause the formula to break.

Here is the math to prove why I am getting the above green dot based on the formula.

Currently there are 4 children each worth 2 points. Greens are worth 2 points, yellow 1 points, and red zero points. I have 1 green 2 yellow and 1 red = 4 points out of 8 points or 0.5 so I get a green.

If you do the math for all 4 children + the 6 sub children you get 10 and the following points are added from the above picture: 1 greens, 4 Yellows and 5 reds = 6 out of 20 points or 0.3 so I would get red in the box.

Mike TV

@J.Barrow

"as CHILDREN only does the items indented not the indented of the indent. or CHILDREN OF CHILDREN. "

Incorrect. You must be doing something wrong with the formula. Example proof:

Column6 formula:

=COUNT(CHILDREN([Primary Column]:[Primary Column]))

Everything with Step followed by a number is a child row and Step 8 is a child of a child row. It wouldn't get 13 if what you said were true. It would only get 12.

Your formula is in the cell with the green dot, is it not? If so, that column has....ZERO children in it. Again, you have to specify the column you want children counted in if it's different than the column your formula is in. CHILDREN() will try counting children in the row that formula is in.

J.Barrow

@Mike TV,

Thanks for your help. I just figured it out. You need to use the formula for DECENDANTS not CHILDREN.

So the formula was: =IF(((COUNTIFS(DESCENDANTS(), "Green") * 2) + COUNTIFS(DESCENDANTS(), "Yellow")) / (COUNT(DESCENDANTS()) * 2) <= 0.49, "Red", IF(((COUNTIFS(DESCENDANTS(), "Green") * 2) + COUNTIFS(DESCENDANTS(), "Yellow")) / (COUNT(DESCENDANTS()) * 2) <= 0.74, "Yellow", "Green"))