If(and(countif equation question

Jeff-s
Jeff-s ✭
edited 12/09/19 in Formulas and Functions

Attempting to make a status bubble roll up where the parent will turn green if there are any blank ("") cells except for when all child cells are blank. Below are some of my failed attempts:

 

IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "") <> COUNT(CHILDREN())), "Green") 

results in blank cell only when all children are filled with a value that is not blank while being green if all children is blank

=IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "") <> 3), "Green")

I tried this to see what the equation is counting. It will be blank when all children are blank but also when all are filled.

 

Thanks

Tags:
· Share on FacebookShare on Twitter

Comments

  • Shaine Greenwood
    Shaine Greenwood Employee

    Hi Jeff,

    COUNT(CHILDREN()) and COUNTIF(CHILDREN()) will only count child cells that have data in them.

    You'll want to reference a parent cell that always has data in it (possibly the Primary column).

    For example:

    =IF(COUNTIF(CHILDREN(), "") = COUNT(CHILDREN([Project Name]1)), "Red",  IF(COUNTIF(CHILDREN(),"") > 0, "Green")

    The first part of this nested IF statement will turn the status Red if all children are blank, as long as there's data in all children for the other parent being referenced (in this example, row 1 of a column titled Parent Name). Otherwise, it will turn the status Green if there's one or more blank children.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!