MAX Date based on value in a Category column

Options

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?



Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!