How to use if function in Excel, including AND, OR, and NOT

excel-if-function

Why if function in excel?

Basically, if functions are used to compare values or to do something when the condition is true or false. This function can also be used with AND, OR, NOT functions. Let us see in detail with an example.

If Function:

Syntax: =if(Condition, "Value if true", "Value if false")

excel-if-function-syntax

For example, we have two salespeople A and B with their daily sales. So, we are going to compare who has sold more products daily.

As mentioned earlier, This function is basically for comparing two or more values. So we use if function to compare A and B's daily sales.

First day sales of A and B are 30 and 29 respectively. Obviously, it is clear from seeing that A has sold more than B. In excel we do it by comparing the cell value "A2" which is A's First-day sale with the cell "B2" which is B's first-day sale. The condition for checking is A2>B2.

Syntax: =if(A2>B2,"A","B")

If the condition is true that is A2>B2 then "A" is printed else "B" is printed.

excel-if-function
If Function with SUM:

This is the same as above instead, we compare SUM value and interpret the result.

In this example, we are going to check whether the daily target is achieved. The daily target is to sell 60 products.

Syntax: =if(SUM(A2:B2)>60,"Done","NOT DONE")

excel-if-function

If they both combined sold 60 products or above the condition is satisfied and it prints "DONE" or else "NOT DONE".

If Function with AND:

This function returns true when both conditions are satisfied and returns false when both or either one conditions are not satisfied.

Syntax: =if(AND(A2>=30,B2>=30),"Yes","No")

excel-if-function

The below example returns true when both A and B have sold more than or equal to 30 and returns false when either A or B have sold less than 30.

If Function with OR:

This function returns true when either one condition is satisfied and returns false if both conditions are not satisfied.

Syntax: =if(OR(A2>=30,B2>=30),"Yes","No")

excel-if-function

The below example returns true when either A or B have sold more than or equal to 30 and returns false when both A and B have sold less than 30.

If Function with NOT:

This function is the same as OR but we add NOT to it. This function returns true when either the conditions are not satisfied and returns false when the conditions are satisfied.

Syntax: =if(NOT(OR(A2>=30,B2>=30)),"x","")

excel-if-function

As you can see, the function returned true since both cell A5 and B5 are not greater than equal to 30.

Nested If Function:

Nested if are used when there are many criteria to compare value. In the below example we are checking the cell A2 value with different criteria.

If A2 value is less than or equal to 9 it returns 1 star, else if A2 value is less than or equal to 19 it returns 2 stars, else if A2 value is less than or equal to 29 it returns 3 stars, else if A2 value is less than or equal to 35 it returns 4 stars, else if all the above conditions are not true then it returns 5 stars. Check the below picture for criteria table.

excel-if-function

Hope you liked this article, if so please share it with your friends.

Detailed Video: How to use if function in Excel, including AND, OR, and NOT.

Share on:
Most Popular