MAX Date based on value in a Category column

TJ Norris

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?

Paul Newcome

You need an "@cell" reference in the CONTAINS function for the "range".

=MAX(COLLECT({Start Date}, {Milestone Category}, CONTAINS("Array Assemblies", @cell)))

Paul Newcome

You need an "@cell" reference in the CONTAINS function for the "range".

=MAX(COLLECT({Start Date}, {Milestone Category}, CONTAINS("Array Assemblies", @cell)))

TJ Norris

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

Paul Newcome

Happy to help. 👍️