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

Michael Stuart

Eric Law

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

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!! 😁

Eric Law

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

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

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

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.

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