How to get Max children in parent row with condition based on partner column

Shannon Petersen

I am trying to get a formula in the parent rows for this sheet (blue rows) in column 2 that returns the max value of children in the 2nd column for that parent but only if that value also corresponds to the max value of parent row in the first column.

So in the screenshot below, for Title 13 row, the results are currently just coming from =MAX(CHILDREN()). And thus I'm getting the highest number of the children regardless of the year it's from. I need to update this sheet so I get the highest value of the children in Column 2 on the condition those children also have the highest value of year in column 1.

Essentially I don't want a value in column 2 to count if the year is 2022 but there's a row with a 2023 entry, the MAX should return only the highest value of the 2023 rows (or whatever the max year is in the parent row for column 1 going forward--I'm not looking to hardcode years in these formulas). So the Max for children of the Title 14 row would ideally be coming up as "3" since it's the max of the 2023 values.

(In short, this sheet was great for seeing the most recent update number for the year until we hit a new year--still learning here.)

I've tried nesting IF, MAX, and CHILDREN combos to no avail, I have also tried using MAX and COLLECT, but all I've learned is that I don't really understand how COLLECT is working.

Paul Newcome

You definitely need a COLLECT function.

Think of the COLLECT as a way to grab a bunch of cells (collecting them) based on range and criteria sets. Similar to a SUMIFS but instead of adding up a bunch of numbers you are gathering together the individual cells to create a "back-end list" or an array.

Once you next this COLLECT function in another function, that other function can look at the array and act accordingly.

=MAX(COLLECT({Range To Pull Together}, {1st Criteria Range}, 1st Criteria))

=MAX(COLLECT(CHILDREN(), CHILDREN([Year Column]@row), @cell = [Year Column]@row))

We COLLECT all of the children where the cell in the [Year Column] is equal to [Year Column]@row which is pulling in the most recent year using a basic MAX function.

Once we have collected all of the CHILDREN with the appropriate year, we evaluate using the MAX function.

Paul Newcome

You definitely need a COLLECT function.

Think of the COLLECT as a way to grab a bunch of cells (collecting them) based on range and criteria sets. Similar to a SUMIFS but instead of adding up a bunch of numbers you are gathering together the individual cells to create a "back-end list" or an array.

Once you next this COLLECT function in another function, that other function can look at the array and act accordingly.

=MAX(COLLECT({Range To Pull Together}, {1st Criteria Range}, 1st Criteria))

=MAX(COLLECT(CHILDREN(), CHILDREN([Year Column]@row), @cell = [Year Column]@row))

We COLLECT all of the children where the cell in the [Year Column] is equal to [Year Column]@row which is pulling in the most recent year using a basic MAX function.

Once we have collected all of the CHILDREN with the appropriate year, we evaluate using the MAX function.

Shannon Petersen

Hi Paul! Thanks for explaining COLLECT that way and sharing the formulas to illustrate-- that makes so much sense now, and your formula solved my issue! Really appreciate you taking the time to answer this!

Paul Newcome

Happy to help. 👍️