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.
Best Answer
-
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.
Answers
-
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.
-
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!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!