Counting Children on Multiple Criteria

Nasir@EBC
Nasir@EBC ✭✭
edited 01/10/20 in Formulas and Functions

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!

Best Answer

Answers

  • Alejandra
    Alejandra Employee
    edited 01/10/20

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

  • 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

  • Paul Newcome
    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!