The Excel IF function is one of the most commonly used functions. It is a conditional function that logically compares a specific value with an expected value. The IF function, which is a core feature of the SUMIF and COUNTIF functions, is the most basic conditional function.
How to Use the Excel IF Function
As defined above, the IF function creates a logical structure and returns values that match specified conditions.
However, you cannot set an unlimited number of conditions. You can use up to 64 conditions, including single, multiple, and mixed conditions.
The number of conditions used in the IF function is not what matters most. The key is not the number of conditions, but how the function is combined with other Excel formula functions.
IF Function Syntax
The syntax of the IF function can be explained separately for single and multiple conditions.
The syntax for a single condition is as follows.
=IF(condition, value if TRUE, value if FALSE)
The syntax for multiple conditions is as follows.
=IF(condition1, value1 if TRUE, IF(condition2, value2 if TRUE, value if FALSE))
Excel IF Function Examples
Let’s look at IF function examples for single, multiple, and mixed conditions.
Excel IF Function: Single Condition
Using a single IF condition, only sales of 1,000 or more are displayed in column F.
The formula used is as follows.
F3 cell =IF(D3>1000,D3,”-“)
Using a single IF condition, only sales by salesperson Steve are displayed in column F.
The formula used is as follows.
F3 cell =IF(B3=”Steve”,D3,”-“)
Excel IF Function: Multiple Conditions
Using multiple IF conditions, 100 is returned if the product is INNER, and 50 is returned if the product is SHIRTS.
The formula used is as follows.
F3 cell =IF(C3=”Inner”,100,IF(C3=”Shirts”,50,”-“))
Using multiple IF conditions, “Money” is returned when the product is INNER and sales are 1,000 or more.
The formula used is as follows.
F3 cell =IF(AND(C3=”Inner”,D3>1000),”Money”,”-“)
IF Function: Mixed Conditions
Using mixed IF conditions, the following formula was created.
- If the product name is INNER and sales are 1,000 or more, return MONEY
- If the product name is INNER or sales are 1,000 or more, return NOVELTY
The formula used is as follows.
F3 cell =IF(AND(C3=”inner”,D3>1000),”Money”,IF(OR(C3=”inner”,D3>1000),”Novelty”,”-“))
Logical Operators (Essential for Conditional Functions)
The following six logical operators are used most often with conditional functions. Be sure to understand and memorize them so you can apply them.
- Value greater than the condition: > condition (e.g., “>100”)
- Value greater than or equal to the condition: >= condition (e.g., “>=100”)
- Value less than the condition: < condition (e.g., “<100”)
- Value less than or equal to the condition: <= condition (e.g., “<=100”)
- Value not equal to the condition: <> condition (e.g., “<>100”)
- Value equal to the condition: condition (e.g., “TEXT” or a number)
AND and OR Conditions
In addition to logical operators, the AND and OR functions are useful in conditional formulas. As shown in the examples above, they are simple to use. However, there is an even simpler method, as shown below.
AND = *
OR = +
In formulas, AND can be replaced with “*”. Here is an example.
=IF(AND(A1>100,B1>100),”Good”,”-“)
=IF((A1>100)*(B1>100),”Good”,”-“)
In formulas, OR can be replaced with “+”. Here is an example.
=IF(OR(A1>100,B1>100),”Good”,”-“)
=IF((A1>100)+(B1>100),”Good”,”-“)
Conclusion
We have covered the Excel IF function. Apply the practical examples of single, multiple, and mixed conditions to your work. We will also cover the expanded conditional functions COUNTIF, COUNTIFS, SUMIF, SUMIFS, MAXIF, and MINIF using many practical examples.
Finally, you need to understand absolute references, which can be used in all formulas, to take full advantage of every formula.