SharePoint List- Calculated column for "IF(AND" statement not returning correct value

Question

SharePoint List- Calculated column for "IF(AND" statement not returning correct value

Harry N Nomikos 1,336

Hi Team,

I've created a calculated column that returns 6 separate value and for some reason it's not returning the correct value when an item;

Is this part of a Remediation Project]="Yes",[Type of WFP Request]="Mailout/Marketing Campaign" =Remediation Event. Instead it's returning "Mailout/Marketing Campaign"

I've provided the formula I've used for my calculated column and highlighted in bold where it should return the word value "Remediation Event"

=IF([Type of WFP Request]="Awareness Only/FYI","Awareness Only/FYI",IF([Type of WFP Request]="Mailout/Marketing Campaign","Mailout/Marketing Campaign",IF(AND([Is this part of a Remediation Project]="Yes",[Type of WFP Request]="Mailout/Marketing Campaign"),"Remediation Event",IF(AND([Type of WFP Request]="Off-Phone Training",[Type of Training]="Organisation e-learn"),"Organisation e-learn Training",IF(AND([Type of WFP Request]="Off-Phone Training",[Type of Training]="Remediation Briefing"),"Remediation Training & Mailout",IF(AND([Type of WFP Request]="Off-Phone Training",[Type of Training]="Mailout/Marketing Briefing"),"Mailout/Marketing Training & Marketing Mailout",""))))))

The below screenshot shows the two conditions have been met, yet the value is not correct

User's image

Any help would be greatly appreciated.

Cheers,
Harry

Zehui Yao_MSFT 5,876 Reputation points

2023-06-16T02:11:35.1366667+00:00

Hi Harry N Nomikos , welcome to the forum, we will do some research on this issue and will get back to you as soon as possible.
Zehui Yao_MSFT 5,876 Reputation points

2023-06-16T08:47:27.9266667+00:00

Hi Harry N Nomikos , may I ask what the purpose of your conditional statement is so that we can figure out the correct formula.
Harry N Nomikos 1,336 Reputation points

2023-06-19T00:24:20.84+00:00

Hi @Zehui Yao_MSFT

The purpose of the formula is return the value of Remediation Event when "Is this part of a Remediation Project" field is selected as "Yes" and when "Type of WFP Request" field="Mailout/Marketing Campaign.

I want it to be integrated into the above formula I provided.

Cheers,
Harry
Zehui Yao_MSFT 5,876 Reputation points

2023-06-19T08:17:49.4433333+00:00

Hi @Harry N Nomikos, since I don't know what you want your formula to do in the whole list, I couldn't give a complete formula that meets your needs.

I infer from the formula provided, due to the IF([Type of WFP Request]="Mailout/Marketing Campaign","Mailout/Marketing Campaign") statement has a higher order of comparison than IF(AND([Is this part of a Remediation Project]="Yes",[Type of WFP Request]="Mailout/ Marketing Campaign"), "Remediation Event") , so any item of Type of WFP request for Mailout/Marketing Campaign will finish after the second condition calculation and return to the Mailout/Marketing Campaign.

For the syntax of the If function in the calculated column, you can refer to this document: https://support.microsoft.com/en-us/office/if-function-7025be14-5665-43d0-af20-8293d1fe9d3a

Hope this helps. If the problem still exists, please feel free to leave a message below. Best Regards,
Harry N Nomikos 1,336 Reputation points

2023-06-20T04:10:19.7+00:00

Hi @Zehui Yao_MSFT

Thanks for the explanation.

The formula does integrate IF with AND statements, and it seems to be returning the correct values, is there any work-around towards this?

I've provided my expected results in Green below to see if this helps

Cheers,
Harry

Accepted answer

0 additional answers

Your answer

Zehui Yao_MSFT 5,876 Reputation points

2023-06-16T02:11:35.1366667+00:00

Hi Harry N Nomikos , welcome to the forum, we will do some research on this issue and will get back to you as soon as possible.
Zehui Yao_MSFT 5,876 Reputation points

2023-06-16T08:47:27.9266667+00:00

Hi Harry N Nomikos , may I ask what the purpose of your conditional statement is so that we can figure out the correct formula.
Harry N Nomikos 1,336 Reputation points

2023-06-19T00:24:20.84+00:00

Hi @Zehui Yao_MSFT

The purpose of the formula is return the value of Remediation Event when "Is this part of a Remediation Project" field is selected as "Yes" and when "Type of WFP Request" field="Mailout/Marketing Campaign.

I want it to be integrated into the above formula I provided.

Cheers,
Harry
Zehui Yao_MSFT 5,876 Reputation points

2023-06-19T08:17:49.4433333+00:00

Hi @Harry N Nomikos, since I don't know what you want your formula to do in the whole list, I couldn't give a complete formula that meets your needs.

I infer from the formula provided, due to the IF([Type of WFP Request]="Mailout/Marketing Campaign","Mailout/Marketing Campaign") statement has a higher order of comparison than IF(AND([Is this part of a Remediation Project]="Yes",[Type of WFP Request]="Mailout/ Marketing Campaign"), "Remediation Event") , so any item of Type of WFP request for Mailout/Marketing Campaign will finish after the second condition calculation and return to the Mailout/Marketing Campaign.

For the syntax of the If function in the calculated column, you can refer to this document: https://support.microsoft.com/en-us/office/if-function-7025be14-5665-43d0-af20-8293d1fe9d3a

Hope this helps. If the problem still exists, please feel free to leave a message below. Best Regards,
Harry N Nomikos 1,336 Reputation points

2023-06-20T04:10:19.7+00:00

Hi @Zehui Yao_MSFT

Thanks for the explanation.

The formula does integrate IF with AND statements, and it seems to be returning the correct values, is there any work-around towards this?

I've provided my expected results in Green below to see if this helps

Cheers,
Harry

Answer 1

Hi @Harry N Nomikos,

Here is the formula I tested in my environment and can achieve the results you expect, please try if it meets your needs.

=IF([Type of WFP Request]="Awareness Only/FYI","Awareness Only/FYI",IF(AND([Is this part of a Remediation Project]="Yes",[Type of WFP Request]="Mailout/Marketing Campaign"),"Remediation Event",IF([Type of WFP Request]="Mailout/Marketing Campaign","Mailout/Marketing Campaign",IF(AND([Type of WFP Request]="Off Phone Training",[Type of Training]="Organisational e-learn"),"Organisational e-learn Training",IF(AND([Type of WFP Request]="Off Phone Training",[Type of Training]="Remediation Briefing"),"Remediation Training & Mailout",IF(AND([Type of WFP Request]="Off Phone Training",[Type of Training]="Mailout/Marketing Briefing"),"Mailout/Marketing Training & Marketing Mailout",""))))))

MicrosoftTeams-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.

Share via

SharePoint List- Calculated column for "IF(AND" statement not returning correct value

0 additional answers

Your answer