Formulas for Multiselect fields

BFuller

Hello,

I am creating a request and process management sheet for our training department. They get requested to develop different training materials for various teams. We need to have a form that other people can fill out and request any combination of different assets (presentations, audio, video, tests, quizzes, etc). The requests will also be for new development or updating existing items.

When those requests are submitted, we can have a different combination of assets listed. One may be audio only while another could say video, quiz while a third could be presentation, video, quiz. So, they could be creating all new assets or updating an existing one.

I created columns on my sheet for each of the assets. This formula works but it is omitting the document status part of new or update and I've not been successful at getting it incorporated=IF(CONTAINS("Announcement", [Assets needed]@row), "5", "0").

So, basically, for each asset column I need something like:

If document status = New training item and

if Assets needed contains "assent name here"

= # of hours

But, if document status = Update existing training and

if assets needed contains "asset name here"

= this # of hours

In the past, I've just done an index/match, but since we have to allow for a multiselect field, I'm not sure how to accomplish this.

Thanks in advance:

Paul McGuinness

Hi @BFuller

This formula should be able to deliver what you need.

=SUM(IF(CONTAINS("Audio", [Column6]@row), Audio#, ""), IF(CONTAINS("Announcement", [Column6]@row), Announcement#, ""), IF(CONTAINS("Powerpoint", [Column6]@row), Powerpoint#, ""))

By using SUM you just treat each section as a separate formula to check what's in the multi select field and apply the appropriate value, and you can expand the formula to include the remaining categories / values.

I have moved the value fields from the columns into summary fields as that reduces the amount of cells on your sheet overall and is easier to maintain if you need to amend down the line, but they will amend all rows including historic ones, which is something to watch out for.

Tested below and working

Hope that helps

Thanks

Paul

BFuller

Thank you so much...this works! I was not familiar with Sheet Summary prior to this suggestion, so I've learned something new!

Another question: I have two scenarios for this formula:

=SUM(IF(CONTAINS("Audio", Audio@row), Audio#, ""), IF(CONTAINS("Announcement", Announcement@row), Announcement#, ""), IF(CONTAINS("PowerPoint", PowerPoint@row), PowerPoint#, ""), IF(CONTAINS("Documentation", Documentation@row), Documentation#, ""), IF(CONTAINS("Quiz/Test", [Quiz/Test]@row), [Quiz/Test]#, ""), IF(CONTAINS("Survey", Survey@row), Survey#, ""), IF(CONTAINS("Video", Video@row), Video#, ""))

If the project is new, I need it to do what I have listed above. And if it is an update I need it to do what I have below:

=SUM(IF(CONTAINS("Audio", Audio@row), Audio2#, ""), IF(CONTAINS("Announcement", Announcement@row), Announcement2#, ""), IF(CONTAINS("PowerPoint", PowerPoint@row), PowerPoint2#, ""), IF(CONTAINS("Documentation", Documentation@row), Documentation2#, ""), IF(CONTAINS("Quiz/Test", [Quiz/Test]@row), [Quiz/Test2]#, ""), IF(CONTAINS("Survey", Survey@row), Survey2#, ""), IF(CONTAINS("Video", Video@row), Video2#, "")) * 8

I need another IF statement, I think, but I can't figure out how to incorporate it. Can you help with that?

Many thanks in advance for your help!!

BFuller

@Paul McGuinness Thank you so much...this works! I was not familiar with Sheet Summary prior to this suggestion, so I've learned something new!

Another question: I have two scenarios for this formula:

=SUM(IF(CONTAINS("Audio", Audio@row), Audio#, ""), IF(CONTAINS("Announcement", Announcement@row), Announcement#, ""), IF(CONTAINS("PowerPoint", PowerPoint@row), PowerPoint#, ""), IF(CONTAINS("Documentation", Documentation@row), Documentation#, ""), IF(CONTAINS("Quiz/Test", [Quiz/Test]@row), [Quiz/Test]#, ""), IF(CONTAINS("Survey", Survey@row), Survey#, ""), IF(CONTAINS("Video", Video@row), Video#, ""))

If the project is new, I need it to do what I have listed above. And if it is an update I need it to do what I have below:

=SUM(IF(CONTAINS("Audio", Audio@row), Audio2#, ""), IF(CONTAINS("Announcement", Announcement@row), Announcement2#, ""), IF(CONTAINS("PowerPoint", PowerPoint@row), PowerPoint2#, ""), IF(CONTAINS("Documentation", Documentation@row), Documentation2#, ""), IF(CONTAINS("Quiz/Test", [Quiz/Test]@row), [Quiz/Test2]#, ""), IF(CONTAINS("Survey", Survey@row), Survey2#, ""), IF(CONTAINS("Video", Video@row), Video2#, "")) * 8

I need another IF statement, I think, but I can't figure out how to incorporate it. Can you help with that?

Many thanks in advance for your help!!

Paul McGuinness

Hi @BFuller

You're welcome, I'm glad it helped.

One of the great things about smartsheet is that regardless of how long using smartsheet we all learn something new every day 👍️

As for your query, the formula below should do the trick, highlighted my formula in bold around your existing formulas. Assumed the reference point was the Development status column, but you can amend as needed if required.

=if(Contains("New",[Development status]@row),SUM(IF(CONTAINS("Audio", Audio@row), Audio#, ""), IF(CONTAINS("Announcement", Announcement@row), Announcement#, ""), IF(CONTAINS("PowerPoint", PowerPoint@row), PowerPoint#, ""), IF(CONTAINS("Documentation", Documentation@row), Documentation#, ""), IF(CONTAINS("Quiz/Test", [Quiz/Test]@row), [Quiz/Test]#, ""), IF(CONTAINS("Survey", Survey@row), Survey#, ""), IF(CONTAINS("Video", Video@row), Video#, "")),if(Contains("Update",[Development status]@row),SUM(IF(CONTAINS("Audio", Audio@row), Audio2#, ""), IF(CONTAINS("Announcement", Announcement@row), Announcement2#, ""), IF(CONTAINS("PowerPoint", PowerPoint@row), PowerPoint2#, ""), IF(CONTAINS("Documentation", Documentation@row), Documentation2#, ""), IF(CONTAINS("Quiz/Test", [Quiz/Test]@row), [Quiz/Test2]#, ""), IF(CONTAINS("Survey", Survey@row), Survey2#, ""), IF(CONTAINS("Video", Video@row), Video2#, "")) * 8,"Other")

Basically I wrapped the two formulas in this if statement, referenced your formulas as 1 and 2 to just show the working around the formula in this case.

=IF(CONTAINS("New", [Development status]@row), "Formula 1", IF(CONTAINS("Update", [Development status]@row), "Formula 2", "Other"))

Hope that helps

Thanks

Paul