# Searching for a value in a multiselect column

Options
✭✭✭✭

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

Tags:

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Looks like you got it. Well done.

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

What are your requirements for the SUMIFS?

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

BINGO!

Thanks again, Paul - I owe you!

I need to get in the habit of using @cell, so thanks for that tip as well.

Have a terrific day.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!