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.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!