How To Use IF Statements In A SharePoint List Calculated Column

HI LUI! I COPIED THE FORMULA THAT YOU EXPLAINED BUT I DO NOT HAVE THE SAME RESULT :(. I Share with you my formula. Thanks!
=IF([Autorizador1]="APROBADO",IF([Autorizador2]="APROBADO","APROBADO","PENDIENTE"))

I kept getting syntax errors and fixed it by replacing the "commas" with "semi-colons"

Hello,
Im currenly stuck with this formula

=IF([ReceiptDetails.receiptCurrency]="EUR", "2000", "Unknown")

I set everything up the column is Calculated but im still having a syntax error

Hi Lui -thanks so much for this.

Can you help me with this what if statement as I am getting a syntax error:
=IF([Urgency]="Immediate",IF([Importance/Impact]="Critical/Intolerable","P1",
IF([Urgency]="Immediate",IF([Importance/Impact]="High","P2",
IF([Urgency]="Immediate",IF([Importance/Impact]="Medium","P2",
IF([Urgency]="Immediate",IF([Importance/Impact]="Low","P3",
IF([Urgency]="Urgent",IF([Importance/Impact]="Critical/Intolerable","P2", IF([Urgency]="Urgent",IF([Importance/Impact]="High","P2",
IF([Urgency]="Urgent",IF([Importance/Impact]="Medium","P3",
IF([Urgency]="Urgent",IF([Importance/Impact]="Low","P4",
IF([Urgency]="Timely",IF([Importance/Impact]="Critical/Intolerable","P2",
IF([Urgency]="Timely",IF([Importance/Impact]="High","P3",
IF([Urgency]="Timely",IF([Importance/Impact]="Medium","P4",
IF([Urgency]="Timely",IF([Importance/Impact]="Low","P4",
IF([Urgency]="No pressure",IF([Importance/Impact]="Critical/Intolerable","P3",
IF([Urgency]="No pressure",IF([Importance/Impact]="High","P4",
IF([Urgency]="No pressure",IF([Importance/Impact]="Medium","P4",
IF([Urgency]="No pressure",IF([Importance/Impact]="Low","P4",))))))))))))))))))))))))))))))))


I would so appreciate your help with this.
Thanks. Una

Lui, thanks for this. I'm getting a syntax error for the following. Can you help me please: =IF([Urgency]="Immediate",IF([Importance/Impact]="Critical/Intolerable","P1",IF([Urgency]="Immediate",IF([Importance/Impact]="High","P2",IF([Urgency]="Immediate",IF([Importance/Impact]="Medium","P2",IF([Urgency]="Immediate",IF([Importance/Impact]="Low","P3",IF([Urgency]="Urgent",IF([Importance/Impact]="Critical/Intolerable","P2", IF([Urgency]="Urgent",IF([Importance/Impact]="High","P2",IF([Urgency]="Urgent",IF([Importance/Impact]="Medium","P3",IF([Urgency]="Urgent",IF([Importance/Impact]="Low","P4",IF([Urgency]="Timely",IF([Importance/Impact]="Critical/Intolerable","P2",IF([Urgency]="Timely",IF([Importance/Impact]="High","P3",IF([Urgency]="Timely",IF([Importance/Impact]="Medium","P4",IF([Urgency]="Timely",IF([Importance/Impact]="Low","P4",IF([Urgency]="No pressure",IF([Importance/Impact]="Critical/Intolerable","P3",IF([Urgency]="No pressure",IF([Importance/Impact]="High","P4",IF([Urgency]="No pressure",IF([Importance/Impact]="Medium","P4",IF([Urgency]="No pressure",IF([Importance/Impact]="Low","P4",))))))))))))))))))))))))))))))))

Would so appreciate your help. Thanks!

Hmmmm. I can't get this to work. What am I missing? =IF(ISBLANK([Predicted Los]), "", IF([Predicted Los]="1-7 DAYS", TEXT([TRIAGE DATE TIME] + 7, "dd-mm-yyyy"), IF([Predicted Los]="8-14 DAYS", TEXT([TRIAGE DATE TIME] + 14, "dd-mm-yyyy"), IF([Predicted Los]="15-21 DAYS", TEXT([TRIAGE DATE TIME] + 21, "dd-mm-yyyy"), IF([Predicted Los]="22-28 DAYS", TEXT([TRIAGE DATE TIME] + 28, "dd-mm-yyyy"), IF([Predicted Los]="29+ DAYS", TEXT([TRIAGE DATE TIME] + 29, "dd-mm-yyyy"), "")))))))

Question: I have an email in a column and I want to fill the other column with another SharePoint list, probable with an ID. Do you have a formula?

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

I have a column that returns a value of 0 and or 1. I wish to create a calc column that returns no for 0 and yes for 1. I need help with how to write this formular. I have created the calc column and tried this function. =IF([Is this a unscheduled job?]=1,"Yes","No") *Is this a unscheduled job?* is the column name that I have the return values for 1 & 0. My new calc column returns No each time regardless. Please point me a the right direction. Thank you in advance.

Thank you so much!

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

Thanks!

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

you're a hero man

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

Thank you
I have question. You capitalized the entire text for high and low in the calculation even though the text in the priority and complexity columns are not capitalized. Is this something I should be doing for any calculation evaluating text fields. I keep getting a syntax error
Thanks

Very Useful....Thanks for the video

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!!

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

I like this but I have a status column that needs to change from pending to completed once 30 other columns have been worked and have a completed status. Yep my head is exploding. How do I look at 30 columns in a row if any one column says pending then keep status pending when all 30 columns have something other than pending then change status for the row from pending to complete. Help please, anyone. Thanks in advance. 😢

HI Lui, Please help me out here.
Lets say I have column Error value this column type is drop down with ( 0,0.1,0.15) - i want to create column error/no error . If my column error value is either 0.1 or 0.15 i want my column error/no error as "error" if its 0 then i want it as "no error" . i used =IF([Error value]="0.1",IF([Error value]="0.15","Error","No error")) but this formula recognized only 0.1 error value as no error and all the other items as error.

I need to calculate the total number of hours entered by a person in the list when he enters the hours (like remaining hours) and should not allow to save if it is crossing the limit of say 50 hours. Can you help me ?

This is great! Thank you for sharing!

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?

Super helpful, thank you!

hello this is a great video I was wondering though if you can do this but with percentages. For example: I have two Columns ( "H1g" and "Avg.H1g"). I want a column that can calculate and say if "H1g" is higher than "Avg.H1g" by 15% it will display as too high. OR if "H1g" is less than "Avg.H1g" by 15% it will display as too low. Let me know if this is possible!!!

Thanks Lui!

You're the real MVP

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?

Hi Lui,
Hope you are doing well !
I need your support:
I have created a sharepoint request log. I have a created date and Completion date column. I want to calculate overall TAT. I created a calculated column"Overall TAT" =Completion date-Created date. It gives me result when I will update the Completion date. I want a formula to put in Overall TAT so that If Completion date is not available then pick today's date. Can you please help?

Another option: I have created today's date column and put a formula: Today's date-Created date but it is not good because few request are already closed.
Please help
Regards,
Dharmender Gautam

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 😢

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.

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.

Hi Lui, is it possible to use calculation on value for the condition in SharePoint List?

=IF([DUE DATE]="0","",IF(OR([PMT TO SOLAR DATE (PMT RELEASE TO SOLAR)]="",[PMT TO SOLAR DATE (EMAIL SENT TO EVELYN/FINANCE APPROVAL)]=""),NOW()-[DUE DATE],"Paid"))

tried this one but it didnt work.
I want if the condition is false = "Paid" and if condition is true, this calculation -> NOW()-[DUE DATE].
Now the false value didnt work, it goes to the calculation only

Thanks Cheers Brazil

Question, how to fix selection contain cell with errors in microsoft lists. Thanks

I am trying to do an if statement in MS list and it not working Can you help thanks

I apply formular in excel is correct but on list on sharepoint do not run correct
Please advise to me
Formular:
=IF((([number days of leave]-INT([number days of leave]))*24)>8,8,([number days of leave]-INT([number days of leave]))*24)
Many thanks
Trinh Duy Tri
From Vietnam

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. :(

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?

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

Is this doable with more than 3 statuses? If not, I'm wondering what kind of trickery I can do by building calculations from other calculation columns, and then hiding them. Messy, but I wonder if it's possible.

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)

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.

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.

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

Hi Lui, I’m trying create a column status, with a calculated formula, that takes the creation date of the document, adds 90 days, after 90 days, the document is expired. I tried =IF([Date de creation]>90,”Expired”,”en cours”) and it doesn’t work. Can you please help me? As you can see the columns are in French… 🤦🏽‍♀️ thank you!!

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