MAX children date if another children column is not blank

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.

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!