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 the 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 AND functions 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 functions are useful but by using them jointly, you can solve some real-life problems. Here is a quick intro for both.

1. 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.
2. 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?

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

How do IF and AND Functions Work?

To combine IF and AND functions you have to just replace the logical_test argument in the 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 the 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 the 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 to the sheet.

If a student is passed 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 the value “Pass”. That means if a student passed both of the subjects then he/she will pass.

But, if both cells have a value lower than 40 then AND will return false, and IF will return the value “Fail”. If a student is failed in any of the subjects he/she will fail.