Counting Children on Multiple Criteria
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 childrow values are EITHER Complete OR N/A, it should consider it as Done only.
Thanks!
Best Answer

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

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

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

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.

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

@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)
Help Article Resources
Categories
Check out the Formula Handbook template!