MAX COLLECT where one range has multiple values



I am using MAX COLLECT to extract from a sheet the most recent date of an event, based on a few criteria. One of the columns can have multiple values (as the source is a multiple value drop down).

The current formula is =MAX(COLLECT({Date}, {Blank}, $[Primary Column]$2, {Section}, [S1]1)) where:

{Date} = date in source range from reference sheet

{Blank} = equipment name from ref sheet, equal to $[Equipment Name & Location]$2

{Section} = area where equipment is from ref sheet, equal to [S1]1

The formula works when {Section} is a single value, but not when there are multiple values in {Section}.

I thought about using CONTAINS for the 2nd criteria but cannot figure it out....any ideas?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!