If(and(countif equation question

Jeff-s

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

Shaine Greenwood

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.