SharePoint List Calculated Column IF Statement [7 Examples]

If you are working with SharePoint calculated columns, this tutorial will be very helpful. In this tutorial, I will explain how to use an if statement in a SharePoint list calculated column. I will show you seven useful examples related to SharePoint calculated column if statement, including SharePoint calculated column if contains text.

The IF statement tests a condition based on the value of a SharePoint Online list column. Whether the condition is true, it returns the true value; otherwise, it returns the false value. Based on the if statement condition, I will show you the four scenarios to include the if statements in a calculated column.

Below are the examples I will cover in this tutorial:

• How to use a Single IF statement in SharePoint Calculated Column
• SharePoint calculated column if contains text
• SharePoint Calculated column IF AND Expression
• SharePoint Calculated Column using Multiple IF AND Conditions
• Use an IF OR Expression in a SharePoint Calculated Column
• SharePoint Calculated Column Nested IF Statements
• IF BLANK() in SharePoint Online Calculated Column

SharePoint Calculated Column IF Statement

Let me explain how to use a single IF statement in a calculated column of a SharePoint Online list, with an example.

I have a SharePoint List, which has a Work progress column (choice type); the choices are:

1. Completed
2. In Progress

I will create a calculated column (Appraisal) based on that column. Using the IF statement, if the user completes the task, they will receive an appraisal as “Achiever“; otherwise, they will receive an appraisal as “Complete soon, to be an achiever.”

Now we will see how to achieve it:

• Open the SharePoint list where you need to add the calculated column using the if statement.
• Now click on the +Add column -> choose See all column types -> Then click on the Next button.

• In the Create column page, provide the column name and choose the type as Calculated (calculation based on other columns) as shown below:

• Now, provide the formula in the formula box using the if statement. Based on my requirement, I provided the formula as shown below:

=IF([Work Progress]="Completed", "Achiever", "Complete soon, to be an achiever")

• The data type returned from this formula is: Choose the data type that the result value should be. Then, click on the OK button.

• Now, open your SharePoint list, and the calculated column will be added as ‘Appraisal’.

This is how to use an if statement in a SharePoint calculated column.

Check out SharePoint Calculated Column Date Examples

SharePoint Calculated Column IF Contains Text

Now, let me show you how to create a calculated column with IF contains text in SharePoint.

Here, I have taken a SharePoint list that has a column named Title, and I will create a calculated column as Product Name:

Here, I will check:

• If Title contains “Laptop”; then Product Name will be Laptop
• If Title contains “Mobile”; then Product Name will be Mobile
• If Title contains “Outlook”; then Product Name will be Outlook, etc.

Follow the steps below:

• Open the SharePoint Online list where you want to add a calculated column if contains text. Then, click on the + Add column option and select See all column types. Then, click on the Next button like below.

• Next, enter the Column name (Product Name) on the Create Column page. And select the Data Type (Calculated).
• Then, enter the IF statement in the Formula box like below.

=IF(ISERROR(FIND("Laptop",Title)),IF(ISERROR(FIND("Mobile",Title)),IF(ISERROR(FIND("Outlook",Title)),"","Outlook"),"Mobile"),"Laptop")

• And, by default, we get the data type returned from the Single line of text. Then, click on the Ok button.

• Once the calculated column is added, we will now add some issues based on the title. Then, we will get the issue-related product name, as shown below.

This is how to use an If Contains Text in a SharePoint calculated column.

Read SharePoint Calculated Column Round Up to Decimal Places

SharePoint Calculated Column IF AND Statement

With an example, let me explain how to use an IF AND statement in a calculated column using the list in SharePoint.

I have a list, which has two columns:

1. Subscription type (Choice type [Premium, Standard, Basic])
2. Netflix Join Month (Date and time type)

I will create a calculated column (Offer) using an ‘And If’ statement based on that column. Suppose the user is using the Premium subscription type and is active in August. They will receive a 20% offer, else No offer as shown below:

Note:

The AND Statement works like, if the both statement are true only it gives an true value.

Now we will see how to achieve it:

• Open the SharePoint list, where you must add the calculated column using the if statement.
• Now click on the +Add column -> choose See all column types -> Then click on the Next button.
• In the Create column page, provide the column name and choose the type as Calculated (calculation based on other columns) as shown below:

• Now, provide the formula in the formula box using the IF AND statement. Based on my requirement, I provided the formula as shown below:

=IF(AND([Subscription Type]="Premium",[Netflix Join Month]="August"),"20% Offer","No Offer")

• The data type returned from this formula is: Choose the data type that the result value should be.
• Then, click on the OK button.

• Now, open your SharePoint list, and the calculated column will be added as ‘Offer’.

This is how to use an ” IF AND statement in a calculated column in a list in SharePoint.

Check out LEN() Function in SharePoint Calculated Column

Use Multiple IF AND Statements in SharePoint Calculated Column

Let’s consider a SharePoint list called ‘Product Issue Tracker List‘, with the following columns as mentioned in the table below.

Based on the ‘Priority‘ and ‘Progress‘ columns, I will create a calculated column [Product Label] that satisfies multiple if and conditions.

Conditions:

• Priority = ‘Important’ AND Progress = ‘Completed’ -> Update Product Label to [Green] else NA.
• Priority = ‘Medium’ AND Progress = ‘Not Started’ -> Update Product Label to [Blue] else NA.
• Priority = ‘Not important’ AND Progress = ‘Not Started’ -> Update Product Label to [Red] else NA.
• Priority = ‘Important’ AND Progress = ‘On Progress’ -> Update Product Label to [Black] else NA.

By following the previous examples, you already know how to create a calculated column in a SharePoint list or library. Let’s start with the following steps.

1. On a Create Column page, provide a column Name as ‘Product Label’ and select data type as Calculated column.
2. In the Formula box, insert the formula placed in the code below. Select the data type returned from this formula as a Single line of text. Then, click the OK button.

=IF(
    AND([Priority]="Important",[Progress]="Completed")
    ,"Green"
    ,IF(
        AND([Priority]="Medium",[Progress]="Not Started")
        ,"Blue"
        ,IF(
            AND([Priority]="Not Important",[Progress]="Not Started")
            ,"Red"
            ,IF(
                AND([Priority]="Important",[Progress]="On Progress")
                ,"Black"
                ,"NA"
               )
           )
      )
  )

3. Go and check your SharePoint list, where you will find the calculated column [Product Label] with the provided values.

This is how you can create a SharePoint calculated column with multiple IF AND statements.

Read SharePoint List Calculated Column Concatenate() Function

SharePoint Calculated Column IF OR Statement

With an example, let me explain how to use an or if statement in a calculated column in a SharePoint online list.

I have a list in SharePoint Online, which has two columns:

1. Number of Products (Number type)
2. Order Distance KM (Number type)

Based on that column, I will create a calculated column (Delivery Charge) using an OR If statement. If the user ordered more than 3 products or if the distance is less than 6 miles, they will receive a Free Delivery charge; else $ 2 delivery charge will be added, as shown below:

Note:

The OR statement works like, in two statements any one of the statement is true only it gives an true value.

Now we will see how to achieve it:

• Open the SharePoint list where you need to add the calculated column using the if statement.
• Now click on the +Add column -> choose See all column types -> Then click on the Next button.
• In the Create column page, provide the column name and choose the type as Calculated (calculation based on other columns) as shown below:

• Now, provide the formula in the Formula box. Based on my requirement, I provided the formula as shown below:

=IF(OR([Number of Products Ordered]>3,[Order Distance Miles]<6),"Free Delivery Charge","2$ Delivery Charge")

• The data type returned from this formula is: Choose the data type as “Single line of text”.
• Then, click on the OK button.

Now, open your SharePoint list, and the calculated column will be added as ‘Delivery Charge’.

This is how to use an if statement in a calculated column using a SharePoint Online list.

Check out Add Leading Zeros to SharePoint List Column using Calculated Column

SharePoint Calculated Column Nested IF Conditions

Suppose you have a SharePoint List with an Employee Performance column (Number type). Based on that column, I will create a calculated column (Appraisal Amount) using a nested If statement.

Based on the employee’s performance, we can provide an appraisal amount as shown below:

1. Employee Performance >= 80% will receive an appraisal amount of 24$.
2. Employee Performance >= 60% will receive an appraisal amount of 18$.
3. Employee Performance >= 50% will receive an appraisal amount of 9$.
4. Employee Performance < 50% will not receive any appraisal amount.

Now we will see how to achieve it:

• Open the SharePoint list where you need to add the calculated column.
• Now click on the +Add column -> choose See all column types -> Then click on the Next button.
• In the Create column page, provide the column name and choose the type as Calculated (calculation based on other columns) as shown below:

• Enter the following formula in the Formula box. Select data type returned from this formula to: “Single line of text”. Click on OK at the bottom of the page.

=IF([Employee Performance]>=80%,"24$ Reward",IF([Employee Performance]>=60%,"18$ Reward",IF([Employee Performance]>=50%,"9$ Reward",IF([Employee Performance]<50%,"No Reward"))))

• Now open your SharePoint list, and the calculated column will be added as Appraisal Amount.

This is how to use a nested if statement in a calculated column using the list in SharePoint.

SharePoint Online Calculated Column IF BLANK Condition

Here, I will take a SharePoint list named “Project Timeline“, which has different types of columns in it. My requirement is to check if the ‘Project Description’ field is blank. If it is, I will update the ‘Project Details’ to ‘Empty’; if not, I will set it to ‘Not Empty.’

To do that, you can create a calculated column in SharePoint by using the ISBLANK() Function.

1. Open your SharePoint list, where you want to create a calculated column, and click on + Add column. Select See all column types -> Click on Next.

2. On the Create Column page, provide a name for the column and select the type Calculated (calculation based on other columns).

3. Then, enter the below-given expression in the Formula box. Ensure that the data type returned by this formula is a Single line of text. Click on the OK button.

4. When you view the SharePoint list, you will see the ‘Project Details‘ column with string values that we have provided. You can see the screenshot below for your reference.

This way, you can use the IF BLANK() function in a SharePoint calculated column.

In this article, I have shown you seven different examples based on IF conditions that you can use in a SharePoint calculated column.

I hope you found this tutorial helpful and interesting. Please let me know in the comments section if you have any questions or suggestions.

You may also like:

• SharePoint Calculated Column With A Hyperlink

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.