Find oldest value where certain conditions exist

Ty Foose

Hi,

I'd love some advice on building a formula. My data includes a column for DATE, a column for STATUS (either "Current" or "Archived"), and a column for TYPE ("Red", "Yellow", or "Blue").

I need a formula that returns the oldest date where both STATUS and TYPE meet the selected criteria, i.e. What is the MIN date for all records that are both "Current" and "Red"?

Thanks!

Paul Newcome

Try this one...

=MIN(COLLECT(Date:Date, Status:Status, "Current", Type:Type, "Red"))

Ty Foose

That works well, thanks. Since my color column was actually a multi select dropdown, I ended up using this:

=MIN(COLLECT(Date:Date, Status:Status, "Current", Type:Type, CONTAINS("Red",@cell)))

Paul Newcome

Try this one...

=MIN(COLLECT(Date:Date, Status:Status, "Current", Type:Type, "Red"))

Ty Foose

That works well, thanks. Since my color column was actually a multi select dropdown, I ended up using this:

=MIN(COLLECT(Date:Date, Status:Status, "Current", Type:Type, CONTAINS("Red",@cell)))

Paul Newcome

Happy to help. 👍️