Counting Children on Multiple Criteria

Nasir@EBC

Hi all

I am using the following formula in a parent row which has 10 child rows:

=IF(COUNTIF(CHILDREN(), "Complete") < 10, "Not Done", "Done")

It shows Done as soon as all the children rows have the word Complete in them.

Now I want this formula to favor 2 conditions for "value_if_true" part of the "IF" function:

if all the child-row values are EITHER Complete OR N/A, it should consider it as Done only.

Thanks!

Paul Newcome

You can wrap the two sets of criteria in an OR function.

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) < 10, "Not Done", "Done")

Alejandra

Hi Nasir,

What value would you like the formula to return if 9 of 10 children rows contain N/A and one is "Complete" or vice versa?

The following formula will return "Done" if all 10 children row are "Complete" or if all 10 children rows are "N/A":

=IF(OR(COUNTIF(CHILDREN(Status1), "Complete") = 10, COUNTIF(CHILDREN(Status1), "N/A") = 10), "Done", "Not Done")

Paul Newcome

You can wrap the two sets of criteria in an OR function.

=IF(COUNTIF(CHILDREN(), OR(@cell = "Complete", @cell = "N/A")) < 10, "Not Done", "Done")

Nasir@EBC

Thanks Alejandra

No matter how many rows contain N/A or Completed, as long as all the rows contain either of these 2 values, the formula should keep counting them.

For example, if 9 rows have N/A and 1 contains Complete, then the formula should return 10.

Natalie Gorman

https://community.smartsheet.com/discussion/comment/236712#Comment_236712

Hello @Paul Newcome

I'm trying to achieve something similar to this formula with Countifs.

Here is my current formula

=IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Yellow")))

I want the formula to return "Red" if all children are all red, or all blue, or a mixture of red and blue.

I've tried various ways but without success.

Your help would be appreciated.

Many thanks

Natalie

Paul Newcome

@Natalie Gorman I would suggest using something along the lines of if the count of green children plus the count of yellow children equals zero, then red.

IF(COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Yellow") = 0, "Red", rest_of_formula)