Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Using formula =MAX(children())

Luis Martini
edited 12/09/19 in Archived 2016 Posts


I'm talking about one Date formated column. When my Parent cell has that formula and I have 4 children's (2 filled and 2 unfilled) the formula returns the MAX value between the 2 filled cells. I want for my Parent the MAX from all four childrens. I need to return the MAX value only when the 4 childrens are filled. What can I do in order to avoid any result while there are any children without information? 


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    Will you always know how many children there are (filled and unfilled)?

    Assuming the answer is "yes - always 4"  then


    =IF(COUNT(CHILDREN()) = 4, MAX(CHILDREN()), "Unfilled remain")


    The formula for "no" is different and longer, but also possible.


    Hope this helps.



  • Travis
    Travis Employee

    Try this:




    This will count the number of children that contain a date (COUNT(CHILDREN())) and add it to the number of children that are blank (COUNTIF(CHILDREN(), ""). If this number is the same as the count of children that contain a date (meaning they all contain a date) then it will show the max. This will work regardless of the number of children. 


    Note - this will look for any value in the children, not just dates. 



  • Luis Martini
    edited 03/03/16

    Thank you both. I'll keep the Travis solution because it's more generic.

    Many thanks.

  • Travis, Luis, J Craig:


    Thank you for the discussion, it enabled me to resolve my roadbloack as well.





This discussion has been closed.