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!
Best Answers
-
Try this one...
=MIN(COLLECT(Date:Date, Status:Status, "Current", Type:Type, "Red"))
-
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)))
Answers
-
Try this one...
=MIN(COLLECT(Date:Date, Status:Status, "Current", Type:Type, "Red"))
-
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)))
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!