IF cell CONTAINS one/two/three different values, return corresponding values
Hello community,
I am attempting to return single or multiple values depending on a multiple dropdown column.
My dropdown column contains criteria "Consolidation", "Reduction", "Termination", "New"
Currently the formula works for single values with the following formula
=IF([Type of Project]@row = "Consolidation", "🝢", IF([Type of Project]@row = "Reduction", "︾", IF([Type of Project]@row = "New", "○", IF([Type of Project]@row = "Termination", "⨷"))))
I would like my formula to be able to return two/three/four of the values. For example if the "[Type of Project] cell had multiple criteria e.g. "Consolidation Reduction" I want my formula to return "🝢 ︾"
Many thanks in advance.
Best Answer
-
Hi @Sam Swain,
You should be able to use this formula to accomplish this:
=IF(HAS([Type of Project]@row, "Consolidation"), "🝢", "") + IF(HAS([Type of Project]@row, "Reduction"), "︾", "") + IF(HAS([Type of Project]@row, "New"), "○", "") + IF(HAS([Type of Project]@row, "Termination"), "⨷", "")
Example output:
Hope this helps - if there are any issues etc. then just post! ☺️
Answers
-
Hi @Sam Swain,
You should be able to use this formula to accomplish this:
=IF(HAS([Type of Project]@row, "Consolidation"), "🝢", "") + IF(HAS([Type of Project]@row, "Reduction"), "︾", "") + IF(HAS([Type of Project]@row, "New"), "○", "") + IF(HAS([Type of Project]@row, "Termination"), "⨷", "")
Example output:
Hope this helps - if there are any issues etc. then just post! ☺️
-
Awesome, that worked perfectly! Thanks Nick!
-
No problem; happy to have helped! 😃
-
Hello!
I have a similar situation however I would like a formula that counts the amount of selections made in a cell and then return a value based on the number of selections.
For example, IF cell contains 4 or more multi select values, then return value.
Thanks,
-
You can use COUNTM to count how many values are selected in a cell - see: COUNTM Function
Then based on that value, you can instruct your formula what to do using IF statements. For example:
=IF(COUNTM([Column Name]@row) >= 4, "Return Value")
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you so much Genevieve that worked perfectly!
Best,
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!