How to get Max children in parent row with condition based on partner column
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.
Help Article Resources
Check out the Formula Handbook template!