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!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!