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

Options

Best Answers

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

    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 βœ“
    Options

    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 βœ“
    Options

    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 βœ“
    Options

    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 ✭✭✭
    Options

    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
    Options

    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.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 πŸŽ‰
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!