Searching for a value in a multiselect column
I probably should be going to bed, but I'm going nuts trying to figure out one last formula.
I have a sheet with a multiselect column named "Project Type".
The column has the following values:
Revenue Opportunity
Revenue Loss Avoidance
Risk Compliance
Cost Saving
I am trying to create a formula along the lines of:
IF Project Type CONTAINS "Risk Compliance" OR CONTAINS "Revenue Loss Avoidance" Then ...., Else ....
For the life of me I just can't get it to work....
Hoping someone can help me out of a jam.
Thanks
Answers
-
This was the formula I was using, until I realized it was only TRUE if only "Revenue Loss Avoidance" or "Risk Compliace" was selected in the Project Type column. If there is more than one value selected, the formula is FALSE
=IF(OR([Project Type]1 = "Revenue Loss Avoidance", [Project Type]1 = "Risk Compliance"), "Return $" + [Return ($)]1 + ", Investment $" + [Investment ($)]1 + ", NET ROI $" + [Net ROI]1, "")
-
I think I may have got it!
=IF(OR(CONTAINS("Revenue Loss Avoidance", [Project Type]1), CONTAINS("Risk Compliance", [Project Type]1)), "Return $" + [Return ($)]1 + ", Investment $" + [Investment ($)]1 + ", NET ROI $" + [Net ROI]1, "")
-
Looks like you got it. Well done.
-
Thanks, Paul. It may not be the most elegant, but it works.
I'm still struggling to wrap my head around working with the multi selection option.
Currently trying to figure out how to SUMIFS when a value from the multiselect is present.
Contains is currently not my friend :(
-
What are your requirements for the SUMIFS?
-
I am trying to sum the value of a column Investment when column Project Type Contains Risk Compliance
(Investment and Project Type are referenced from a different sheet)
I'm struggling with the correct usage of CONTAINS
Something along these lines, but I'm obviously not using contains correctly
=SUMFIS({Investment}, {Project Type}, CONTAINS("Risk Compliance"))
Thanks in advance for the help.
-
You are actually very close. You just need a "range" in your CONTAINS function. For this you would use "@cell". @cell simply tells the formula to evaluate each cell within the previously established range on a cell by cell basis.
=SUMFIS({Investment}, {Project Type}, CONTAINS("Risk Compliance", @cell))
-
BINGO!
Thanks again, Paul - I owe you!
I need to get in the habit of using @cell, so thanks for that tip as well.
I truly appreciate your help.
Have a terrific day.
-
Happy to help. 👍️
-
I hate to use up all my questios in one day, but I have one my headscratcher (for me, at least) if you don't mind.
I am attempting to sum (from a different sheet) the column INVESTMENT if AREA contains [Metric1]89, and PROJECT TYPE contains [Metric1]84, or [Metric1]85, and TARGET COMPLETION Q is 2020-Q1
=SUMIFS({Investment}, {Area}, CONTAINS([Metric 1]89, @cell), {Project Type}, OR(CONTAINS([Metric 1]$84, @cell), {Project Type}, CONTAINS([Metric 1]$85, @cell)), {Target Completion Q}, ="2020-Q1")
I am getting an INVALID DATA TYPE ERROR, though not for all areas.
Some of these areas with the error should be $0, while others should have a value in Investment. Can't quite figure out what is throwing the error.
Any thoughts?
Thank you, once again.
-
and sure enough - write after I post that, I went back to re-try the formula, building and testing in steps, and VIOLA! it worked
=SUMIFS({Investment}, {Area}, CONTAINS([Metric 1]87, @cell), {Target Completion Q}, ="2020-Q1", {Project Type}, OR(CONTAINS([Metric 1]$85, @cell), CONTAINS([Metric 1]$84, @cell)))
Now to figure out where I went wrong the first time :)
Thanks again!
-
The problem with the first one is where you went...
OR(range, criteria, range, criteria)
instead of the way you did it in your working formula of...
range, OR(criteria, criteria)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!