How To Use IF Statements In A SharePoint List Calculated Column

This SharePoint tutorial is going to demonstrate how to use IF statements in a SharePoint list. Specifically, this SharePoint List tutorial will demonstrate how to create a calculated column in a SharePoint list that contains IF statements. SharePoint list IF statements allows you to set a calculated column value based on whether a condition evaluates to true or false. This SharePoint List calculated column IF statement tutorial will demonstrate several different ways that you can use IF statements in a SharePoint list:

First, it will demonstrate how to create a SharePoint list calculated column IF statement that evaluates a single condition. That is, IF a condition is true, the SharePoint list calculated column will be set to value 1 and if false, it will be set to value two.

Second, it will demonstrate how to create a SharePoint list calculated column IF AND statement. That is, IF two or more conditions evaluate as true, the SharePoint list calculated column will be set to value 1 and if false, it will be set to value two.

Lastly, it will demonstrate how to use SharePoint List calculated column multiple IF statements. Specifically, this will demonstrate how to nest or use multiple IF statements in a SharePoint list calculated column formula. That is, IF condition one is true, set the column to value 1, IF condition two is true, set the value to 2, IF condition three is true, set the value to 3.

An important note, when using SharePoint list multiple IF statements, you can only incorporate up to a maximum of 7 IF statements.

SharePoint list calculated field IF statements are an excellent way to incorporate data validation in a SharePoint list and also to streamline data entry by collecting attributes subject to the values entered into other columns.

DOWNLOAD THE FORMULAS USED IN THIS VIDEO:
All of the formulas used in this SharePoint List IF Statement tutorial can be accessed here:

🔔SIGN UP FOR MY SHAREPOINT LIST FUNDAMENTALS COURSE🔔:

Timeline
0:00 - Start
0:25 - Overview of the SharePoint List IF Statements covered in this tutorial
1:30 - SharePoint list calculated column IF statement
5:40 - SharePoint list calculated column IF AND statement
10:10 - SharePoint list calculated column multiple IF statements

#SharePoint #Microsoft365 #microsoftteams

-MORE ABOUT ME-

LOOKING FOR TRAINING OR CONSULTING - CONTACT ME AT:

LETS BE FRIENDSI

GOT A VIDEO IDEA? FILL OUT THIS FORM:

SIGN UP FOR MY MONTHLY NEWSLETTER

PRODUCTS THAT I USE TO CREATE MY YOUTUBE VIDEOS:

SOFTWARE THAT I USE TO CREATE MY YOUTUBE VIDEOS & CONTENT:

OTHER SERVICES THAT I USE:

Note that I may earn a small commission at no additional cost to you from purchases made using these links.

[tomrwills]

Thank you. The multiple IF statements was right on the money for my scenario.

[yakem]

I have been searching for this exact video for days, every other one makes you use flows. Thank you so much

[elisiasaam-quinty]

Thank you so much!!! I appreciate how you present the material: carefully and methodically but not "talking down" to people.

Question: Can I use an IF statement in a calculated column like this to return the value of another field or is that getting into LookUp territory?

[alcopopgaming]

Is it possible for multiple choice columns to print multiple calculated outputs?

[richardchen]

Thank you so much for this, exactly what I have been looking to achieve. Are you able to advise how I can add a condition where if [Effort in Hours]=0 (or is nil) then ignore? I currently have IF([Priority]*[Probability]*[Proximity]*[Impact]<=4, "Escalate to Workstream but Priority and Probably could be zero if this is logged as an 'issue' (I used your step-by-step show/hide column so those two value will not be populated) TIA x

[rhiannasteindl]

Hi Lui, thanks for the video.
I was wondering, could you use the IF formaula to provide a numeric value based on a text value? The company I work for allocates out applications and they are allocated various point values. Would it be possible to create a column to calculate the point value based on the application status?
I couldn't quite figure out how to adapt your example above to this. The parameters we have are, If Status = closed, points=0, and if Status "Sent to Endorser" or "Sent to Delegate", the allocated points value = 50% of allocated value
I had a go but wasn't sure how to get values to divide
=IF([Status]="Closed", "0", IF(AND[Status]="Sent to Endorser", "[Status]="Sent to Delegate", "value/2"))
Would you be able to offer any advice? Thank you so much.

[MVPDeven]

Hi, How to change the DDMMYYY format of a date column to MMDDYYYY ?

[lisadrinkwalter]

Follow-up...I ended up using =[PM M1]-[Process Monthly Target], which works ok; however I'd rather just make the PM M1 a colour (each month's actuals whether on-target or not) rather than create a separate calculated column as this will create too many columns after 12 months and I believe there is a limit on calculated columns. would love your assistance if possible.

[caleblauber]

Hey Lui, great video.
I am hitting a similar snag with my situation...

I am trying to generate a task "Status" via a calculated column rather than drop down method.

I have 3 date columns, created on, due date and completed on.
1. If the completed on date is fulfilled, it should say "Completed".
2. If today is greater than the due date AND completed on is still empty, it should say "Overdue".
3. Finally, if today is less than the due date and completed is still empty, it should say "In Progress".

I'm a bit rusty, but my excel formula that worked is breaking in SharePoint. :(

[mesutcerci]

Thank you for demonstration. I just want to ask what if the value of Effort in Hours set to 250 what will be the result For the nested IF statement that you have written at the last? Is it will show Error or It will not shown anything?

[martinargimon]

Hi Lui, I've been battling with the formula to check two (2) if statements conditions. I have the following formula that works OK =IF([Start Date]<[Due By], "Pending", "Overdue"), although I want to change it a three (3) condition statement whereas the first 'IF" statement will check if the column "Completed Task" says "YES" or "NO", if it says "YES" then i want to automatically enter in the new calculated column "CLOSED", if NOT i want the formula to check the column named "start date" against the "due date" and if the Start date is less than the due date the formula must put "Pending" otherwise "Overdue"... Perhaps I coudl send you a 'clip" ( Printscreen " for you to check ??? Pls advice. Many thanks

[lisadrinkwalter]

Thanks...I'm looking to compare the cell value in two columns..should be easy, but can't find the solution - your demo was close. Something like this in calculated column formula (365 Sharept List): =IF([PM M1] value >[Process Monthly Target] value, "On Track"), IF(AND([Process Monthly Target] value < [PM M1] value, "Below Target".

Originally looking to make the target column change colour red if below the value, but can't find that solution either, the conditional format setting is limited and won't compare to values, in two columns. I hope you can help. Thank you.

[joannburroughs]

Hi Lui, , I have been struggling with a formula that would contain 4 nested IF(AND scenarios. I am attempting to build the formula to reflects: IF "days in" is <30="Gate 1", if "days in" <=180 and "Y" in the "survey received" column="Gate 2", if "days in" <=211 and "Y" in the "follow up" column= "Gate 3", if "days in" >211 and "Y" in "decision complete" column="Gate 4". Could you please advise on how to make this type formula work?

[AndresLopez-uobk]

Hi Lui, I am stuck in writing a calculated column. The column has a range of blood alcohol content ranging from 0.00-0.500. I originally created this calculation:

=IF([Breath Test Result]<=0.08, "Legal Limit", "Illegal Limit")

however, it only worked for 2 arguments and not accounting for the N/A selection. After watching you amazing video, I then adjusted to this one:

=IF([Breath Test Result]<0.80, "Legal", IF(AND([Breath Test Result]>0.081, [Breath Test Result]<=0.500), "Illegal", IF([Breath Test Result]="n/a", "no results)))

but SharePoint is telling me that the syntax is wrong. Can you please help?

[sebastianvaazquez]

Hello Lui first of all thank you for your videos. Can we create a calculated Colin based on date? For example if([end date]> today, “due”, “not due”) I’m getting syntax error 😢

[donalddrinkwater]

I am trying to display a calculated value "number" that is listed as one of the unsupported fields that conditional formulas. Basically I need the hours submitted to be "Approved Hours" which is the calculated field, once the Supervisor approves the request.

[luiscastillotantachuco]

Hello Lui, I am looking for a query to show fields based on a multiple selection, for example I have an options field, where I select A, it shows me the fields, but also that when I select B, it also shows them. I have A, B, C, D in the options and I only want it to show when I select A or B

[sheereenameerdeen]

Hi Lui, Very informative video.Can you pls help with the syntax for the conditions below,
If 99.7 then Large OR
If 99.5 then Medium OR
If 98.0 then Small

[alphatazmaniac]

I am trying to figure out how to calculate Travel Comp TIme for a worker, I normal Start Time Column and Normal End Time Column, I have variuous tarvel legs example Travel Leg 1 depart travel leg 1 arrive, we also have over wait time column Comp Time is not paid for over 2 hours of wait time. How woudl i compare the travel legs to the normal start end work times to properly calculate travel comp times? My Columns are NST (Normal Start Time) NET (Normal End TIme) OL1D (Out Leg 1 Depart) OL1A (Out Leg 1 Arrive) My 2 other issues will be non work days, travel on holidays Non work days full time is compenasated minus over 2 hours wait time. holidays no travel compensation is given. (I know that's alot)

[customcruiserroy]

Thank you for posting the video. I got it to work once. But I need help if you can. I used =IF([AGENT COUNTRY]="AFGHANISTAN", "APAC") and it worked for Afghanistan filling in next column to APAC. But I need descriptions for several more countries. How do I add other countries such as USA should fill in column with NAM? Thanks!!