Subject: Adding weighting/points to answers within a multi select dropdown?

Best Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer βœ“

    Hello @Michael Stuart , This is a not fun answer.

    You will need helper columns for all of your dropdowns. Basically for your 2. question, have columns 2 plane, 2 ec, 2 train, and each of those you can do an =IF(Contains([2 question]@row, "type"), # value). Then you can sum it. I would recommend that you rename your question column to say Q1, Q2, etc... and in the form it expands on the question. It would make writing these a lot easier.

  • Michael Stuart
    Michael Stuart ✭✭
    Answer βœ“

    Hi @Eric Law , thank you for coming back to me... I thought this might be the case, of creating multiple columns, but was wondering if there was a smarter way. Thank you again and for the tip in terms of question headers that makes total sense in streamlining the formulas!! 😁

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer βœ“

    Hello @Michael Stuart , This is a not fun answer.

    You will need helper columns for all of your dropdowns. Basically for your 2. question, have columns 2 plane, 2 ec, 2 train, and each of those you can do an =IF(Contains([2 question]@row, "type"), # value). Then you can sum it. I would recommend that you rename your question column to say Q1, Q2, etc... and in the form it expands on the question. It would make writing these a lot easier.

  • Michael Stuart
    Michael Stuart ✭✭
    Answer βœ“

    Hi @Eric Law , thank you for coming back to me... I thought this might be the case, of creating multiple columns, but was wondering if there was a smarter way. Thank you again and for the tip in terms of question headers that makes total sense in streamlining the formulas!! 😁

  • Michael Stuart
    Michael Stuart ✭✭

    Hi @Eric Law thanks again for support me with this... I have managed to get the formula to work within the same sheet, however instead of having the scoring in the main sheet, I want to create a separate sheet for the scoring tally. I have attempted this but faced issues referencing another sheet.


    I have tried to include the name column (primary) into the formula but it doesn't like it. any further support would be welcomed.

    Is "=IF(Contains" still the right formula to use when referencing another sheet?

  • Michael Stuart
    Michael Stuart ✭✭
    edited 04/25/23

    Hi @any Community, just wondering if anyone can support with answering the second part to the above, where I am looking to creating a new metrics sheet to reference the original sheet? If anyone needs any further detail please don't hesitate to content me

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Michael Stuart

    You could just do this in one column in your main source sheet, like so in the Score column:

    Β =IF(HAS("Plane (-3 points)", [2. Select all relevant modes of transport?]@row, -3) + IF(HAS("Electric Car (3 points)", [2. Select all relevant modes of transport?]@row, 3) + IF(HAS("Train (3 points)", [2. Select all relevant modes of transport?]@row, 3)


    This is using + to add together each individual score based on each selection. It would need to be done in the source sheet because you're creating calculations on an individual row-by-row basis, evaluating that specific cell.

    If you don't want to show the score in this main sheet, you could hide the score column then use a Report to show the Name and the Score together, if that helps!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!