SharePoint Calculated Column If Statement [4 Examples]

In this SharePoint Online tutorial, I will show you how to use the if statement in a calculated column in a SharePoint Online list. With examples, we will go through “SharePoint Calculated Column If Statement” and how to use “sharepoint calculated column multiple if conditions“.

The IF statement tests a condition based on the SharePoint online list. Whether the condition is true, it returns the true value, else, 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.

In 4 scenarios, I will demonstrate using a single if statement in a calculated column. I will especially explain how to use if and statement in a calculated column, use if or statement in a calculated column, and use nested if statements in a calculated column in a SharePoint Online list.

SharePoint calculated column if statement

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

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 completed the task, they will receive appraisal as “Achiever“, else they will receive 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 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, the calculated column will be added as Appraisal.

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

SharePoint calculated column and if

With an example, let me explain how to use an if and statements 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 And If statement based on that column. If the user using the Subscription type Premium and is using it in August month. 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 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, the calculated column will be added as Offer.

This is how to an and if statements in a calculated column in a list in SharePoint.

SharePoint calculated column or if

With an example, let me explain how to use an or if statement in a calculated column in 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 OR If statement. If the user ordered a product more than 3 products or if the distance is less than 6miles, they will receive a Free Delivery charge else “2dollars 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 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 or statement. 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, that the result value should be.
• Then, click on the OK button.

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

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

SharePoint calculated column nested if

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

I 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 performance we can provide 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 using the if statement.
• Now click on the +Add column -> choose See all column types -> Then click on the Next button.
• In 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([Employee Performance]>=80%,"24$ Reward",IF([Employee Performance]>=60%,"18$ Reward",IF([Employee Performance]>=50%,"9$ Reward",IF([Employee Performance]<50%,"No Reward"))))

• 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, 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 using if blank Function

We will discuss how if blank function is used in the SharePoint Online calculated column.

Here, I will create a Calculated column named Month that calculates and provide a text value of just the Month & Year (i.e. Aug 2023) based on the data from the column Travel Start Date.

• For Example, we have taken a SharePoint Online List (Employee Travel Request) -> Click on +Add column
• In this Create column window, select See all column types -> Click on Next.

• Name and Type = Provide a name for the column (Month).
• Select the type Calculated (calculation based on other columns).

• Additional column settings = Specify the detailed options for the type of information you have selected.
• Description = Here describe the column.
• Insert column = Select the column that is used in the formula.
• Formula = Insert the formula of the required function.

Syntax of SharePoint Online Calculated Column using if blank function listed below:

=IF(ISBLANK([Value]),"",TEXT([Value], "mmm yy"))

• For this, I have used columns of data type (Date & Time) i.e. Travel Start Date.

You can see the formula for SharePoint Calculated Column using if blank function is mentioned below.

=IF(ISBLANK([Travel start date]),"",TEXT([Travel start date],"mmm yy"))

• By default, the data type returned from this formula is a Single line of text. But we have changed it to Date and Time data types as per our needs.
• Click on the OK button.

• The below screenshot shows the output for the inserted formula of the SharePoint calculated column using the if blank function.

Here, we have seen how SharePoint Online calculated columns using if blank function is implemented.

SharePoint Calculated Column using if(and) Function

This section will see how SharePoint calculated column if(and) will be used.

For that, follow the steps listed below.

• Open a SharePoint Online List -> Click on the +Add column.

• In this Create a column window, click on Next.

• In this window, give the Column name and select the type of information in this column.

• For this SharePoint Online calculated column, I have used columns Priority and Progress of Choice data type.

You can see the formula for SharePoint Online Calculated Column using if(and) function mentioned below.

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

• Here, insert the formula for the if(and) condition by inserting two columns as shown below.
• Select the returned type data from this formula -> Click on OK.

• In the below screenshot, you can see the output of the SharePoint Calculated Column using the if (and) condition.

This is how the SharePoint Online Calculated column using if (and) condition is implemented.

Conclusion

Here, I have shown you the four different scenarios of how to use the single if statements in a calculated column and how to use if and statements in a calculated column using the list in SharePoint Online.

Moreover, using a SharePoint Online list, I have explained how to use if or statement in a calculated column and use nested if statement in a calculated column.

You may also like:

• SharePoint calculated column random number
• SharePoint calculated column get month from date
• SharePoint calculated column: difference between two dates
• SharePoint calculated column blank date