How to Combine IF and AND Functions in Excel

 

As I told you, by combining IF with other functions you can increase its powers. AND function is one of the most useful functions to combine with IF function.

Like you combine IF and OR functions to test multiple conditions. In the same way, you can combine IF and AND functions.

There is a slight difference in using OR & AND function with IF.

In this post, you will learn to combine IF & AND functions and you will also learn why we need to combine both of these.

 

Quick Intro

Both of these function are useful but by using them jointly, you can solve some real-life problems. Here is a quick intro for both.

• IF Function – To test a condition and return a specific value if that condition is true or another specific value if that condition is false.
• AND Function – To test multiple conditions. If all the conditions are true then it will return true and if any of the conditions are false then it will return false.

Why is this important?

• You can test more than one condition with IF function.
• It will return a specific value if all the conditions are true.
• Or, it will return another specific value if any of the conditions is false.

How IF and AND Functions Work?

To combine IF and AND functions you have to just replace logical_test argument in IF function with AND function. By using AND function you can specify more than one condition.

Now, AND function will test your all conditions here. If all the conditions are true then AND function will return true and IF function will return the value which you have specified for true. And, if any of the conditions is false then AND function will return false and IF function will return the value which you have specified for false. Let me show you a real-life example.

Examples

Here I have a marks sheet of students. And, I want to add some remarks in the sheet.

If a student is passed in both of the subjects with 40 marks or above, the status should be “Pass”
And, if a student has less than 40 marks in both of the subjects or even in one subject, the status should be “Fail”.
The formula will be.
=IF(AND(B2>=40,C2>=40),”Pass”,”Fail”)

In the above formula, if there is a value 40 or greater than in any of the cells (B2 & C2) AND function will return true, and IF will return value “Pass”. That means if a student is passed in both of the subjects then he/she will pass. But, if both cells a has a value lower than 40 then AND will return false and IF will return value “Fail”.
If a student is failed in any of the subjects he/she will fail.

Sample File