IF cell CONTAINS one/two/three different values, return corresponding values

Sam Swain

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.

Nick Korna

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! ☺️

Nick Korna

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! ☺️

Sam Swain

Awesome, that worked perfectly! Thanks Nick!

Nick Korna

No problem; happy to have helped! 😃

Dylan Eggers

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,

Genevieve P.

Hi @Dylan Eggers

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

Dylan Eggers

Thank you so much Genevieve that worked perfectly!

Best,