Find oldest value where certain conditions exist

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!

Find more posts tagged with

Accepted answers

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)))

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.