How 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.
So let’s get started.
Table of Content
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 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
Last Words
AND is a useful function but solely not in use much. But if you combine IF and AND functions they are super helpful.
Especially, when you want to test more than one conditions and it’s important to meet all the conditions, as I have used in above example.
Over To You
So what do you think?
Do you have any unique idea to use IF and AND Functions?
Share with me in the comment box.