MAX Date based on value in a Category column
Hello. I am trying to calculate MIN and MAX dates based on the value in the Milestone Category. The first screenshot is my source sheet. The second screenshot is the second sheet where I am performing the calculations.
Looking at the first screenshot below, let's say I want to calculate the MAX Start Date for any row that contains "Array Assemblies", I would expect a returned value of 3/23/23.
If I use the following formula, it is only looking at row #49 and returning an incorrect value of 3/10/23:
=MAX(COLLECT({Start Date}, {Milestone Category}, "Array Assemblies"))
If I use the following formula, it returns a blank value for some reason:
=MAX(COLLECT({Start Date}, {Milestone Category}, CONTAINS("Array Assemblies", {Milestone Category})))
Can someone please give me some advice on how to best do this?
Best Answer

You need an "@cell" reference in the CONTAINS function for the "range".
=MAX(COLLECT({Start Date}, {Milestone Category}, CONTAINS("Array Assemblies", @cell)))
Answers

You need an "@cell" reference in the CONTAINS function for the "range".
=MAX(COLLECT({Start Date}, {Milestone Category}, CONTAINS("Array Assemblies", @cell)))

You're the man! Thanks, @Paul Newcome!

Help Article Resources
Categories
Check out the Formula Handbook template!