Correct IF, AND conditional formulas

Question

Hello,

I am fairly new to Sharepoint Lists and I would like some help with a conditional formula please.

I have a column that I would like to show ONLY if 2 other multi choice columns both have specific options chosen. I am having trouble using the AND function in a conditional formula.

So far I have, but does not seem to work:

=if(AND([$TotalNumberofPICs]== '2', if[$TypeOfTSL] == 'GSL', 'true', 'false'))

Can someone please help correct my conditional formual?

Thanks so much,

Aleisha

Answer 1

Dear Aleisha,

Thanks for your updates and sorry for my mistake.

When I copy the formula, I mistakenly add a comma after || of the GSL part.

You may try the formula as follows to see the result.

=if(([$_x0024_TotalNumberofPICs]=='2'||[$_x0024_TotalNumberofPICs]=='3'||[$_x0024_TotalNumberofPICs]=='4'||[$_x0024_TotalNumberofPICs]=='5'||[$_x0024_TotalNumberofPICs]=='6'||[$_x0024_TotalNumberofPICs]=='7'||[$_x0024_TotalNumberofPICs]=='8'||[$_x0024_TotalNumberofPICs]=='9')&&([$_x0024_TypeOfTSL]=='GSL'||[$_x0024_TypeOfTSL]=='VRSL'||[$_x0024_TypeOfTSL]=='LPSL'||[$_x0024_TypeOfTSL]=='SPSL and LPSL'),'true','false')

Thanks for your effort and time!

Sincerely,

Cliff | Microsoft Community Moderator

Answer 2

Hi Cliff,

Thank you for your further reply!

I have checked the field names and they are correct, I have tried the about formula you suggest the second time and it works beautifully.

Both TotalNumberofPICs and TypeofTSL columns are choice, but not multi choice, if I was to adapt the formula to show other options as true, could you confirm the below formula is correct?

=if([$TotalNumberofPICs]=='2'|[$TotalNumberofPICs]== '3'||[$TotalNumberofPICs] == '4'||[$TotalNumberofPICs]== '5'||[$TotalNumberofPICs]== '6'||[$TotalNumberofPICs]== '7'||[$TotalNumberofPICs]== '8'||[$TotalNumberofPICs]== '9'&&[$TypeOfTSL]=='GSL'||[$TypeOfTSL]=='VRSL'||[$TypeOfTSL]=='LPSL'||[$TypeOfTSL]=='SPSL and LPSL','true','false')

I have tried this (and logically based on my very little knowledge) it should work, however the column still shows as true when any of the TotalNumberofPICs column is selected alone, rather than TotalNumberofPICs AND TypeofTSL.

Any ideas why?

I appreciate your help :)

Thanks,

Aleisha

Answer 3

Dear Aleisha,

Thanks for your updates for confirmation.

As you are using the Conditional Formula feature, you may use the internal field names first. For example, you create the Choice column without multiple-choice enabled (The feature doesn't support the Choice column with multiple-choice enabled) TotalNumberofPICs and TypeOfTSL. You can get the internal field name by clicking on List Settings>clicking on the filed and get the internal field name from "Field=" as follows.

In my environment, they are _x0024_TotalNumberofPICs and _x0024_TypeOfTSL.

Now type the formula as following on the Conditional Formula edit window of 2 COK Holder.

=if([$_x0024_TotalNumberofPICs]=='2'&&[$_x0024_TypeOfTSL]=='GSL','true','false')

Welcome to share any updates at your convenience if you need further help on this issue.

Thanks for your effort and time!

Sincerely,

Cliff | Microsoft Community Moderator

Answer 4

Hi Cliff,

Thank you for your reply.

I have tried the formula you have suggested, copied and pasted and it sharepoint list says "Enter a valid Condition"?

Answer 5

Dear Aleisha,

You may use the formula as follows.

=IF(AND([$TotalNumberofPICs]="2",[$TypeOfTSL]="GSL"),"true","false")

Welcome to share any updates at your convenience if you need further help on this issue.

Thanks for your effort and time!

Sincerely,

Cliff | Microsoft Community Moderator