How to Combine IF and AND Functions in Excel

HomeExcel FormulasHow 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.

sample-file.xlsx

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.

how to combine if and and functions replace logical test

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.

how to combine if and and functions table to add status

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”)

how to combine if and and functions insert formula

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.

sample-file.xlsx

⇠ Back to Excel Formulas List

6 thoughts on “How to Combine IF and AND Functions in Excel”

  1. I’d like to show an icon (▶) in the cell beside the next line for entry in a tracker I’m working on. I’m sure it’s a nested something. The arrow should show when the row is blank and not show when the row has information (text) This half works, formula is in cell B6:
    =IF(AND(C5=””,C7=””),””, “▶”)
    However, after the row is filled with data, the arrow remains. It should not show in the completed row and should show on the next row. Nested functions really hurt my brain 😒. Can you help?

    Reply
  2. I need help with formulae in Excel.
    Condition: a) If basic > 15000, then calculate PF on basic * 12%
    b) If basic plus some of the components > 15000, restrict to 15000 then calculate PF @ 12% on 15000.
    c) If basic plus some of the components < 15000 then calculate PF @ 12%.

    Please help me giving this formulae

    Reply
  3. Quotations for eg. “ ” are needed if there is text involved in the result. Is that Also needed if for Some reason the result is a figure?

    Reply

Leave a Comment