How To Use IF Statements In A SharePoint List Calculated Column

[@LuiIacobellis]

GET THE EXAMPLE FORMULAS REFERENCES IN THIS VIDEO: bit.ly/3RZPJWe 🔔SIGN UP FOR MY SHAREPOINT LIST FUNDAMENTALS COURSE🔔bit.ly/4f0yZXS

[@tomrwills]

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

[@LuiIacobellis]

You're very welcome and thanks for watching! Glad I could help!

[@yakem226]

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

[@LuiIacobellis]

You're very welcome! I'm glad my content was able to help you! Thanks for watching

[@andersonbervanger2311]

You're the real MVP

[@LuiIacobellis]

Thank you for the kind words

[@alcopopgaming7601]

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

[@elisiasaam-quinty9811]

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?

[@LuiIacobellis]

You're very welcome, I'm glad you found this helpful. To answer your question, it sounds more like a use case for a lookup column. Think of an if statement as a control where you determine what value gets populated based on input in some other column. If you only want to display a value of another column, then a lookup would simply let the user select a value that corresponds or relates to and displays said value. Hope this and thank you for watching

[@saraarnold854]

Super helpful, thank you!

[@LuiIacobellis]

Glad it was helpful! Thank you for watching

[@s.n.senthilnarayanan749]

Very Useful....Thanks for the video

[@LuiIacobellis]

You're very welcome and thanks for watching

[@sandramerlo9458]

This is great! Thank you for sharing!

[@LuiIacobellis]

I'm glad you found this helpful Sandra!

[@CybrZone]

you're a hero man

[@LuiIacobellis]

@CybrZone You're very welcome! Thanks for watching!

[@user-jm8mf5nt3p]

Thank you so much!

[@LuiIacobellis]

You're welcome!

[@SideHustleTraveler]

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

[@richardchen420]

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]

[@rhiannasteindl5730]

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.

[@LuiIacobellis]

Hello, my apologies for the delay in responding to your comment. I hope that you found an answer by now. If not, I would suggest that you separate calculations in their own calculated column and then reference them in the calculated column you’re building. Trying to incorporate too much in one calculated column can be unmanageable. Also, I would suggest posting the specifics of your query over on the Microsoft Tech community as someone may be able to help troubleshoot your code. The website is: Home - Microsoft Community Hub - Hope this helps!

[@wrm8460]

Thanks Lui!

[@LuiIacobellis]

You're very welcome and thanks for watching

[@lisadrinkwalter9764]

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.

[@LuiIacobellis]

Hi Lisa, sorry for the delay. I've developed a formula that I think meets your requirements. You can access it at this link: docs.google.com/document/d/1WyA9Bue3xsptLwgqWSgGqdt9JBFP4qXtO9ZvEYqKiRI/edit?usp=sharing I also have a video demonstrating how to implement conditional formatting, which you can use to change the color red if below the value. That video is here: kzbin.info/www/bejne/d5eYaquEo7NpqNU Hope this helps and thanks for watching!

[@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)

[@LuiIacobellis]

Hi Kevin, apologies for the delay. Your use case sounds somewhat complicated and it may be best served by creating a flow in Microsoft Power Automate. It is much easier to build out the logic require to handle your calculations in Power Automate than in a SharePoint list using just calculated columns. That being said, it sounds like you’d need to built out a calculated column to look at the difference between Normal Start AND End, and then the difference between your travel leg. You can create a calculated column that looks at the difference in working days using a formula such as the one found on this site: sharepoint.stackexchange.com/questions/197827/calculate-number-of-working-days-between-two-dates As for holidays, again this would be really messy to try and execute using just SharePoint list columns. I’m not aware of a formula that allows for holidays to be extracted given its variable based on location. Hope this helps and thanks for the comment.

[@caleblauber6613]

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

[@LuiIacobellis]

Hi Caleb, sorry for the delay, I believe if you use the samples that I provided in the google doc posted in the description of this video and add in the following formula to test the blank condition: = ISBLANK([Title]) you should be able to get it working. Hope this helps and thanks for watching!

[@MVPDeven]

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

[@rayt61]

Thanks!

[@LuiIacobellis]

Tim, thank you so much for your generosity! I'm glad you found this video helpful.

[@luiscastillotantachuco2585]

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

[@LuiIacobellis]

Hi there unfortunately I can't assist with individual requests via chat. Id suggest posting your specific use case over on the Microsoft Tech Community or alternatively try using an generative AI app such as Chat gpt or Copilot.

[@donalddrinkwater2971]

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.

[@LuiIacobellis]

Hi Donald, I'm not quite following your scenario. It sounds like you are trying to track the "status" of the hours and not the values itself. I do have a tutorial outlining how you can implement a status column in an SP List here: kzbin.info/www/bejne/mGTXkoZ5r994prc - Hope this helps!

[@ddp2049]

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

[@LuiIacobellis]

Hi Josed, can you elaborate on the error? Thanks,

[@felipesignorellireis7839]

Thanks Cheers Brazil

[@LuiIacobellis]

Felipe Signorelli Reis You're very welcome! Thanks for watching!

[@lisadrinkwalter9764]

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.

[@LuiIacobellis]

Hi Lisa, sorry for the delay. I've developed a formula that I think meets your requirements. You can access it at this link: docs.google.com/document/d/1WyA9Bue3xsptLwgqWSgGqdt9JBFP4qXtO9ZvEYqKiRI/edit?usp=sharing I also have a video demonstrating how to implement conditional formatting, which you can use to change the color red if below the value. That video is here: kzbin.info/www/bejne/d5eYaquEo7NpqNU Hope this helps and thanks for watching!

[@dondeestamel]

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?

[@LuiIacobellis]

Unfortunately I do not have a formulas to cover this

[@Simo-un2zu]

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.

[@LuiIacobellis]

Hi there, you can nest up to 7 conditions in an if statement with multiple checks. Even then, there are work arounds available to be able to exceed this limit. The work arounds do require the creation of calculated columns with formulas however. If you end up having complex requirements in this regard, it may warrant exploring whether you can use Power Apps to simplify (if that is an option of course). Thanks for watching!

[@Simo-un2zu]

@@LuiIacobellis Thank you, Lui. I'll try a few things and see what I can come up with, cheers :)

[@LuiIacobellis]

@@Simo-un2zu You're very welcome! Let me know how it goes!

[@martinargimon730]

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]

[@LuiIacobellis]

Hi Martin, please send me your formula via email. Please also send a screenshot of the full list with column names visible. My email is [e-mail]. I'll do my best to reply in a few hours. Thanks!

[@ddp2049]

I am trying to do something similar but in MS lists

[@sathyakamaraj4922]

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.

[@LuiIacobellis]

Hi there, the approach that you are taking requires that you insert three IF statement conditions. You are currently using two and attempting to include an else. You can use the sample formulas I included in the video description and use the last sample to build this out. You'll need to clearly specify all three conditions. Hope this helps and thanks for watching

[@nithin573]

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 ?

[@LuiIacobellis]

Hi Nithin, I would suggest you post your question over on the Microsoft Tech Community as you will likely get a faster response. If you include the column names etc. that would help - techcommunity.microsoft.com/ Thanks!

[@sheereenameerdeen4842]

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

[@LuiIacobellis]

Hi there, unfortunately I can't support these types of questions via KZbin comments. If you are looking for 1-1 support, you can contact me here: bit.ly/3Tea7Uc

[@LuiIacobellis]

PS - ChatGPT is a great tool to help with these types of queries :)

[@mesutcerci2725]

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?

[@LuiIacobellis]

Hi there, thanks for the kind words. This is a good point. In my nested IF statement formula, if you enter 250, it will return "0" which is the default when none of the conditions are satisfied. My formula should have an >= in it. Thanks for the question & for watching!

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

[@LuiIacobellis]

Hi there, you will need to nested IF statements which I do cover in the tutorial. I also have a link to samples from the tutorial in the video description which you could use as a starting point to help cover this use-case. Hope this helps! Thanks for watching!

[@ddp2049]

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

[@LuiIacobellis]

Hi there, I'd need some more information to assist. Thanks for the comment and for watching!

[@nikkijoy226]

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

[@nikkijoy226]

Tried capitalized and it worked 😊

[@LuiIacobellis]

Sorry for the delay and glad you resolved this issue!

[@rayt61]

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.

[@LuiIacobellis]

Hi Tim, this formula =IF([Is this a unscheduled job?]=1,"Yes","No") - should work provided that your reference column "Is this a unscheduled job" is a number type column. If your reference column is a single line of text, then the condition =1 should be ="1". Further, it might make sense to just have your reference column be a choice type column with the options "yes" and "no". If you were assigning numerical values to each answer, you could then build your calculated column to reference "yes" and "no". Hope this helps!

[@rayt61]

@@LuiIacobellis Thank you,

[@LuiIacobellis]

You're welcome

[@trinhtri6608]

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

[@LuiIacobellis]

Hi there, if you are looking for assistance with building out IF statements in SharePoint, I would suggest you visit the Microsoft Tech Community and post your question there: techcommunity.microsoft.com/ Hope this helps and thanks for watching!

[@sebastianvaazquez4181]

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 😢

[@LuiIacobellis]

Hi there, you're welcome. I do have a tutorial that outlines that use case. You can access it here: kzbin.info/www/bejne/kJvWf4xthpaFf8k - Hope this helps!

[@sofiazulkifli]

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

[@LuiIacobellis]

Hi there, it is possible to use calculation on the condition, but this starts to add complexity to your formula. It would be easier to implement separate calculated columns for your conditions that you can then reference in your formula. Hope this helps and thanks for watching!

[@trishae5635]

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

[@LuiIacobellis]

Unfortunately you can only use up to 7 nested IF statements in a calculated column. This should be pretty easy to do within a Microsoft Power Automate flow. Hope this helps and thanks for watching

[@AndresLopez-uo2bk]

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]

[@LuiIacobellis]

Hi Andres Lopez there, I would suggest posting this question over on the Microsoft Tech Community as you will likely be able to get a fast and more detailed response. You can access it at this link: techcommunity.microsoft.com/ - Hope this helps and thanks for watching!

[@catarinaarruda3757]

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

[@LuiIacobellis]

Hi Catarina, your formula looks correct. I think you might need to break this down into three columns. You will want to create a calculated column that takes the created date and adds 90 days. This would =[Created]+90. That column represents your expiration date. Then, you want to create another column that calculates the current date =Today. Then you can build a calculated column that checks whether Expiration Date is less than today. Hope this helps!

[@catarinaarruda3757]

@@LuiIacobellis thank you! I’m gonna try it out!

[@LuiIacobellis]

You're very welcome

[@AmadayMata]

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

[@LuiIacobellis]

Hi Amaday Mata , I would suggest posting this question over on the Microsoft Tech Community as you will likely be able to get a fast and more detailed response. You can access it at this link: techcommunity.microsoft.com/ - Hope this helps and thanks for watching!

[@dharmendergautam5181]

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

[@LuiIacobellis]

Hi Dharmender, Sharepoint list formulas are not dynamic meaning that they do not update in real time. They only update when items are created or modified. As such you might need to create a workflow in Power Automate to check the list and update it periodically. Hope this helps and thanks for watching

[@LuiIacobellis]

CHECK OUT MY HOW TO USE SHAREPOINT PLAYLIST - 40+ TUTORIALS: kzbin.info/aero/PLmE7KGV9-I4uibXaJ7ZqTXbbS4tukRJPh

[@DArkar7814]

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

[@LuiIacobellis]

Hi there, I would suggest trying Microsoft Copilot or ChatGPT! You can use it to write your formulas and they usually work!

[@joannburroughs9091]

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

[@LuiIacobellis]

Hi there, sorry for the delay! if you are looking for assistance with building out IF statements in SharePoint, I would suggest you visit the Microsoft Tech Community and post your question there: techcommunity.microsoft.com/ Hope this helps and thanks for watching!