Forum Discussion
Solved
Seeking help with a formula for a Calculated Column
Hello,
Have a situation with a Calculated Column in SharePoint Online, was hoping someone could provide some insight:
Have a Choice field called Question 1. Possible choices are:
- Answer 1
- Answer 2
- Answer 3
Each possible answer will have a score associated it. For example:
- Answer 1 - 100 points
- Answer 2 - 100 points
- Answer 3 - 50 points
* These are CHECKBOXES so more than one answer can be selected *
Have a Calculated Column called Score 1 - and for each answer box checked, we want to SUM the total points for Question 1, based on the different checkboxes that were checked for that question. Having a tough time with that calculation.
I am able to do this fine with other Question choice fields that are RADIO BUTTONS, because only one can be selected:
For example:
=IF([Question 1]="Answer 1",100,IF([Question 1]="Answer 2",100,IF([Question 1]="Answer 3",50,0)))
MY QUESTION:
How can I sum up all the points and populate Score 1 with the SUM of the points for ALL of the answer CHECKBOXES selected?
Thanks in advance...
As @ganeshsanap stated, the calculated column does not have a way to evaluate a multi-choice column. However, you can accomplish this using custom list formatting. The Score will not be stored in SharePoint, but will be visible in the views as it is calculated live.
SharePoint List Formatting King, Chris Kent(@thechriskent), has a blog post to walk you through how to do it. List formatting can be a little daunting, but if you are working with calculated columns, the leap is not too bad. Chris has tons of examples to accomplish all kinds of list formatting magic and is the moderator of the PnP List Formatting Samples repository.
Here is the blog post mentioned above: https://thechriskent.com/2019/02/07/formatting-multi-select-values-in-list-formatting/
Here is a link to the PnP List Formatting Samples site: https://pnp.github.io/List-Formatting/
Good luck! Don
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
As @ganeshsanap stated, the calculated column does not have a way to evaluate a multi-choice column. However, you can accomplish this using custom list formatting. The Score will not be stored in SharePoint, but will be visible in the views as it is calculated live.
SharePoint List Formatting King, Chris Kent(@thechriskent), has a blog post to walk you through how to do it. List formatting can be a little daunting, but if you are working with calculated columns, the leap is not too bad. Chris has tons of examples to accomplish all kinds of list formatting magic and is the moderator of the PnP List Formatting Samples repository.
Here is the blog post mentioned above: https://thechriskent.com/2019/02/07/formatting-multi-select-values-in-list-formatting/
Here is a link to the PnP List Formatting Samples site: https://pnp.github.io/List-Formatting/
Good luck! Don
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
- Thank you Don,
This sounds like an approach that we should look into. We are also looking into a Flow to update the score column, but the PnP sounds encouraging as well.
Thanks again to all who took a shot at this one!
Pandra This is not possible using SharePoint out of the box capabilities as calculated columns does not support multiple selection choice columns in formulae.
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
