SharePoint List- Calculated column to return 3 separate values

Question

SharePoint List- Calculated column to return 3 separate values

Harry N Nomikos 1,336

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 names User's image

Thanks Team,
Harry

Answer accepted by question author

1 additional answer

Your answer

Answer 1

@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

Harry N Nomikos 1,336 Reputation points

2023-05-22T02:44:24.3833333+00:00

Hi @AllenXu27

Hope you had a great vacation holiday :)

The above formula works perfectly- thank you again my friend :)

Regards
Harry

Answer 2

AllenXu-MSFT 24,981 Moderator

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.

Harry N Nomikos 1,336 Reputation points

2023-05-18T06:45:39.12+00:00

Hi @AllenXu-MSFT

Thanks for the above formula, I only want it to return the expected result values if they equal "Yes" in the SharePoint List columns I listed above. If they return another value other than "Yes" I want it to return "No Readiness activities"

Hope that makes sense and apologies for any confusion.

Kind Regards
Harry
AllenXu-MSFT 24,981 Reputation points Moderator

2023-05-18T07:28:00.3933333+00:00

@Harry N Nomikos ,

Thanks for your reply.

So you only want the calculated column returns the expected results in only 3 conditions listed in your original post, right? And any other conditions, the calculated column returns "No Readiness activities".

Use this:

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

Apologize in advance if I still understand. Kindly let me know :)
Harry N Nomikos 1,336 Reputation points

2023-05-18T11:29:18.2866667+00:00

Hi @AllenXu-MSFT

The above 3 conditions was an example of what values could show up within those 3 SharePoint columns.

The 3 SharePoint columns are single choice drop-down options and I want them to return those expected values (highlighted blue column on my original post) when "Yes" is selected. If "Yes isn't selected or if the column is left blank, I want the value to return "No Readiness activities".

Hope that makes sense and I look forward to your response.

Kind Regards
Harry
Harry N Nomikos 1,336 Reputation points

2023-05-18T23:38:40.5633333+00:00

Hi @AllenXu-MSFT
Not sure if you got my previous msg above as I didn't get notified when this post was edited, but hopefully this clarifies what I'm after.

Thanks again and I look forward to your response

Cheers,
Harry

Harry N Nomikos 1,336

Hi @AllenXu-MSFT

Just so there's no confusion, I've provided the drop down options and an example of what values will show across these 3 columns

Drop down choice options

Will training be required for this change? = 'Yes' & 'No'

🎯Has BIA/HLIA been scheduled= 'Yes' & 'Not yet scheduled'

📣Is Comms required= 'Yes' & 'No'

Task	Will training be required for this change?	🎯Has BIA/HLIA been scheduled	📣Is Comms required	Expected Result
Project 1	No	Yes	Yes	BIA, Comms
Project 2	Yes	Not yet scheduled		Training
Project 3	Yes	Yes	Yes	Training, Comms
Project 4	Yes	Yes	No	Training, BIA
Project 5		Yes	Yes	BIA, Comms
Project 6	No	Not yet scheduled		No Readiness activities

Regards,
Harry

AllenXu-MSFT 24,981 Reputation points Moderator

2023-05-22T01:59:49.24+00:00

@Harry N Nomikos ,

I have post a new answer below, take a reference to it and feel free to let me know if you still need any help.

Share via

SharePoint List- Calculated column to return 3 separate values

1 additional answer

Your answer