SharePoint List- Calculated column to return 3 separate values

Harry N Nomikos 1,266 Reputation points
2023-05-18T01:51:43.43+00:00

Hi Team

I require a formula to return upto 3 separate values that come from 3 separate columns on my SharePoint List.

Below is a snipped of my expected result and I've provided the SharePoint column namesUser's image

Thanks Team,
Harry

SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
2,291 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,211 questions
0 comments No comments
{count} votes

Accepted answer
  1. AllenXu-MSFT 18,041 Reputation points Microsoft Vendor
    2023-05-22T01:54:51.2133333+00:00

    @Harry N Nomikos ,

    Sorry for my late reply and I was just back from vocation. Thanks for your cooperation again! And now I totally get you.

    I believe this is what you need:

    =IF(AND([Will training be required for this change]="Yes",OR([Has BIA/HLIA been scheduled]="No",[Has BIA/HLIA been scheduled]="",[Has BIA/HLIA been scheduled]="Not yet scheduled"),[Is Comms required]="Yes"),"Training,Comms",IF(AND([Will training be required for this change]="Yes",[Has BIA/HLIA been scheduled]="Yes",[Is Comms required]="Yes"),"Training,BIA,Comms",IF(AND(OR([Will training be required for this change]="No",[Will training be required for this change]=""),[Has BIA/HLIA been scheduled]="Yes",[Is Comms required]="Yes"),"BIA,Comms",IF(AND(OR([Will training be required for this change]="No",[Will training be required for this change]=""),OR([Has BIA/HLIA been scheduled]="No",[Has BIA/HLIA been scheduled]="",[Has BIA/HLIA been scheduled]="Not yet scheduled"),[Is Comms required]="Yes"),"Comms",IF(AND([Will training be required for this change]="Yes",[Has BIA/HLIA been scheduled]="Yes",OR([Is Comms required]="",[Is Comms required]="No")),"Training,BIA",IF(AND([Will training be required for this change]="Yes",OR([Has BIA/HLIA been scheduled]="No",[Has BIA/HLIA been scheduled]="",[Has BIA/HLIA been scheduled]="Not yet scheduled"),OR([Is Comms required]="",[Is Comms required]="No")),"Training",IF(AND(OR([Will training be required for this change]="No",[Will training be required for this change]=""),[Has BIA/HLIA been scheduled]="Yes",OR([Is Comms required]="",[Is Comms required]="No")),"BIA","No Readiness activities")))))))

    User's image


1 additional answer

Sort by: Most helpful
  1. AllenXu-MSFT 18,041 Reputation points Microsoft Vendor
    2023-05-18T06:37:25.4733333+00:00

    Hi @Harry N Nomikos ,

    Apply the below formula to your list calculated column.

    =IF(AND([Will training be required for this change]=TRUE, [Has BIA/HLIA been scheduled]=TRUE, [Is Comms required]=TRUE), "Training,BIA,Comms", IF(AND([Will training be required for this change]=TRUE, [Has BIA/HLIA been scheduled]=FALSE, [Is Comms required]=TRUE), "Training,Comms", IF(AND([Will training be required for this change]=FALSE, [Has BIA/HLIA been scheduled]=TRUE, [Is Comms required]=TRUE), "BIA,Comms", IF(AND([Will training be required for this change]=FALSE, [Has BIA/HLIA been scheduled]=FALSE, [Is Comms required]=TRUE), "Comms", IF(AND([Will training be required for this change]=TRUE, [Has BIA/HLIA been scheduled]=TRUE, [Is Comms required]=FALSE), "Training,BIA", IF(AND([Will training be required for this change]=TRUE, [Has BIA/HLIA been scheduled]=FALSE, [Is Comms required]=FALSE), "Training", IF(AND([Will training be required for this change]=FALSE, [Has BIA/HLIA been scheduled]=TRUE, [Is Comms required]=FALSE), "BIA", "")))))))
    
    

    Note that the three columns ([Will training be required for this change], [Has BIA/HLIA been scheduled] and [Is Comms required]) are all Yes/No type.

    Test result from my side.

    User's image


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.