Searching for a value in a multiselect column

Alan Rappa · 07/23/20

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

Alan Rappa · 07/23/20

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

Alan Rappa · 07/23/20

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

Paul Newcome · 07/23/20

Looks like you got it. Well done.

Alan Rappa · 07/23/20

https://community.smartsheet.com/discussion/comment/257427#Comment_257427

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 :(

Paul Newcome · 07/23/20

What are your requirements for the SUMIFS?

Alan Rappa · 07/23/20

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.

Paul Newcome · 07/23/20

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

Alan Rappa · 07/23/20

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.

Paul Newcome · 07/23/20

Happy to help. 👍️

Alan Rappa · 07/23/20

@Paul Newcome

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.

Alan Rappa · 07/23/20

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!

Paul Newcome · 07/23/20

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)

Searching for a value in a multiselect column

Answers

Help Article Resources

Categories