# MAX children date if another children column is not blank

Options

I am having an issue with a formula. I need to capture the children's max date for Closed Date only if all children's Location cells that are not blank have a Close date.

With the sample below

1. Currently, the Parent Close date would be blank as not all of the Locations 1-4 have a Close Date.
2. If all four locations had dates, the MAX date would be provided
3. The rows below Location 4 are omitted as the Location is blank

I hope I explained this well. Thank you in advance.

• ✭✭✭✭✭✭
Options

Give this a try:

=IF(COUNTIFS(CHILDREN(Location@row), NOT(ISBLANK(@cell)), CHILDREN([Close Date]@row), ISDATE(@cell)) = COUNTIFS(CHILDREN(Location@row), NOT(ISBLANK(@cell))), MAX(COLLECT(CHILDREN([Close Date]@row), CHILDREN([Close Date]@row), ISDATE(@cell))))

• ✭✭✭✭✭✭
Options

Give this a try:

=IF(COUNTIFS(CHILDREN(Location@row), NOT(ISBLANK(@cell)), CHILDREN([Close Date]@row), ISDATE(@cell)) = COUNTIFS(CHILDREN(Location@row), NOT(ISBLANK(@cell))), MAX(COLLECT(CHILDREN([Close Date]@row), CHILDREN([Close Date]@row), ISDATE(@cell))))

• Options

That worked. Thank you very much for your time.

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!