How to use the IF function in Excel

1. Syntax

Syntax:  (condition, value 1, value 2)
Meaning : If 'condition' is true, the function returns 'value 1', otherwise it returns 'value 2'.

 2. Some notes when using 

- In the IF function formula, "Value 1" is the result when the condition is true, and "Value 2" is the result when the condition is false.

- If any parameter in IF is an array, the function will evaluate each part of that array.

- You can nest up to 64 IF functions inside each other. However, other functions such as VLOOKUP or HLOOKUP can be used instead, to optimize the conditions.

- To count quantities based on conditions, use the COUNTIF or COUNTIFS function.

- To sum based on conditions, use the SUMIF or SUMIFS function.

3. How to use

For example: Suppose there is a list of 7 students with available test results. If the score is 5 or higher, the student passes; below 5, the student must retake the test. We can use the IF function to determine the results.

In this case, we use the IF function with a basic condition: if true, one result occurs, if false, another result occurs. Specifically, if the test score is greater than or equal to 5, the student passes; otherwise, the student retakes the test.

Formula: =IF(Score >= 5, "Pass", "Retake")

Step 1:  In this table, the results will be displayed in the "Ranking" column (column F). Therefore, you need to enter the formula in column F to determine whether the student passes or retakes the exam.

The value to refer to is the "Score" column (column E), which is the column containing the conditions for considering the results.

Thus, the formula =IF(E8>=5,"Pass","Retake") will be entered into cell F8, corresponding to the first candidate in the list.

- E8 is the score of the first candidate (M01), used as a condition for passing or retaking the exam.

- >=5 is the criterion to determine the result. If the score is 5 or higher, the candidate passes; otherwise, the candidate must retake the exam.

Step 2:  After pressing Enter , cell F8 will display the result "Pass" because the score at E8 satisfies the condition >= 5 . 

Step 3:  Just move your mouse to the lower right corner of cell F8 (the mouse pointer will change to a + sign ), then drag down to apply the formula to the remaining rows. Excel will automatically calculate and display the results "Pass" or "Retake" according to each corresponding score.

For all candidates, if >= 5 points, it will show passed, otherwise it will show retake .

4. Summary of examples

Given a student's transcript, consider the results to see if the student passed or had to retake the exam.
With the following data table:
Average Score >=5: Pass
Average Score

We use the formula for cell D6: =IF(C6>=5,"Pass","Retake")
You have the results of students who passed or retook the exam.

With the above specific examples, you must have a better understanding of the syntax and usage of the IF function in Excel. In addition, you can refer to the article COUNTIFS function - Conditional statistical function in Excel to improve your knowledge.

David Pac

Update 20 February 2025