How would I combine multiple "yes" columns into a singular column grouping?

Options

I am using Smartsheet to create a contract builder. Part of the Smartsheet asks which additional services they'd like included. Those services are each listed under their own row and ask users if they want to add that service. Example: Column A asks if you want to add Weekend Service, Column B asks if you want to add Double Service, Column C asks if you want to add Night Service.

If someone chooses "YES" for two of those and chooses "NO" for the third; Could there be a column named "Additional Services Requested" and have that column list: "Weekend Service, Double Service"? If we included the cost of those services, could we have another column add the prices for only the "YES" selections?

Snippet of the columns I'm hoping to use attached:

Thank you very much!

Best Answer

  • Brandon Sills
    Brandon Sills ✭✭✭✭
    Answer ✓
    Options

    Hi Brett,

    One option would be:

    Insert a new "Helper Column" for each Service. This will be to calculate if the service is chosen. In that Helper Column input the formula:

    =IF([Service A]@row = "Yes", SUM([Cost A]@row, ""))

    Add an additional column to sum the values in the helper columns. This will give you the total for the "Yes" selections.

    Add additional Helper Columns for each service and input the formula:

    =IF([Service A]@row = "Yes", "Service A", "")

    This will populate the helper columns with the Service Names that are chosen.

    Next, add an additional column to join the helper columns containing the Service Names chosen and input the formula:

    =JOIN(COLLECT([Service A Request Helper]@row:[Service C Request Helper]@row, [Service A Request Helper]@row:[Service C Request Helper]@row, @cell <> ""), ",")

    I will add a screenshot to assist.

    Once setup, you can hide the helper columns.

    Hope this helps.

    Brandon

Answers

  • Brandon Sills
    Brandon Sills ✭✭✭✭
    Answer ✓
    Options

    Hi Brett,

    One option would be:

    Insert a new "Helper Column" for each Service. This will be to calculate if the service is chosen. In that Helper Column input the formula:

    =IF([Service A]@row = "Yes", SUM([Cost A]@row, ""))

    Add an additional column to sum the values in the helper columns. This will give you the total for the "Yes" selections.

    Add additional Helper Columns for each service and input the formula:

    =IF([Service A]@row = "Yes", "Service A", "")

    This will populate the helper columns with the Service Names that are chosen.

    Next, add an additional column to join the helper columns containing the Service Names chosen and input the formula:

    =JOIN(COLLECT([Service A Request Helper]@row:[Service C Request Helper]@row, [Service A Request Helper]@row:[Service C Request Helper]@row, @cell <> ""), ",")

    I will add a screenshot to assist.

    Once setup, you can hide the helper columns.

    Hope this helps.

    Brandon

  • Brett FSR
    Options

    @Brandon Sills That worked wonderfully! It's going to make our sheet look like a well oiled machine.

    Thanks so much Brandon, I am very grateful for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!