Dec 06 2021 09:06 AM
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:
Each possible answer will have a score associated it. For example:
* 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...
Dec 06 2021 11:11 PM
@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.
Dec 07 2021 07:45 AM
SolutionAs @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.
Dec 07 2021 09:44 AM
Dec 07 2021 07:45 AM
SolutionAs @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.