IF with AND function in Excel

In this article, we will learn IF with AND function in Excel.

What is AND criteria ?

In Excel, we extract results based on the given criteria. For Example we need to extract the list of employees who are married and have salaries above half a million dollars per annum. The above criteria can be more difficult when there are more than 2 criterias. So Let's learn the AND function which checks each criteria and returns only and only if all stated criteria stands true. Returns False if any of the conditions is false. Use the IF function with AND function to get customized results for the required result.

IF - AND Function in Excel

IF function in Excel is used to check the condition and return value on the basis of it.

Syntax:

=IF(Logic_test, [Value_if True], [Value_if_False])

AND function works on logic_test. It helps you run multiple conditions in Excel. If every one of them is True, then only AND function returns True else False.

Syntax:

=AND( Logic_test 1, [logic_test 2], ..)

Using the above two functions we can use the below formula:

Generic formula:

=IF(AND(condition1, condition2,...),value if true, value if false)

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here We have a list of Scores and We need to know under which criteria it lies.

Here multiple criterias are needed to satisfy. Use the formula to match the given criteria table

Formula:

=IF(AND(B3 > 50, B3 < 60), "Below Average",IF(AND(B3 > 60, B3 < 90),"Average",IF(AND(B3 > 90,B3< 100),"Above Average","Top Score")))

Copy the formula in other cells, select the cells taking the first cell where the formula is already applied, use shortcut key Ctrl + D

We got the Results corresponding to the Scores.

You can use IF and AND function to meet multiple conditions in a single formula.

Another example

In this example, we will get the customized result based on the given criteria. Here We have this table of apple’s color and type.

If color is “Red” and type is “Juicy” then write OK in column D. Else type Not OK.

Write this IF AND formula in D2 column and drag it down.

=IF(AND(B3="Red",C3="Juicy"),"OK","Not OK")

And you can see now that only apples that are Red and Juicy are marked OK.

How It Works

IF function: You know how IF function in Excel works. It takes a boolean expression as the first argument and returns one expression IF TRUE and another if FALSE. Learn more about The Excel IF Function.

=IF(TRUE or FALSE, statement if True, statement if false)

AND Function : Checks multiple conditions. Returns TRUE only if all conditions are TRUE else returns FALSE.

=AND(condition1, condition2,....) ==> TRUE/FALSE

In the end, AND function provides IF function TRUE or FALSE argument and based on that IF prints the result.

Alternate Solution

Another way to do this is to use nested IFs for Multiple Conditions.

=IF(B3="Red",IF(C3="Juicy",”OK”,”Not OK”),”Not OK”)

Nested IF is good when we want different results but not when only one result. It will work but for multiple conditions it will make your excel formula too long.

Here are all the observational notes using the IF - AND function in Excel
Notes :

Make sure each condition in the formula works fine for the number values before using for all the other values.
Operations like equals to ( = ), less than equal to ( <= ), greater than ( > ) or not equals to ( <> ) can be performed within a formula applied, with numbers only.

Hope this article about IF with AND function in Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

How to Count Cells That Contain This Or That in Excel in Excel :To cells that contain this or that, we can use the SUMPRODUCT function. Here's how you do those calculations.

IF with OR function : Implementation of logic IF function with OR function to extract results having criteria in excel data.

IF with AND function : Implementation of logic IF function with AND function to extract results having criteria in Excel.

How to use nested IF function : nested IF function operates on data having multiple criteria. The use of repeated IF function is nested IF excel formula.

SUMIFS using AND-OR logic : Get the sum of numbers having multiple criteria applied using logic AND-OR excel function.

COUNTIFS With OR For Multiple Criteria : Count cells having multiple criteria match using the OR function. To put an OR logic in COUNTIFS function you will not need to use the OR function.

Using the IF with AND / OR Functions in Microsoft Excel : These logical functions are used to carry out multiple criteria calculations. With IF the OR and AND functions are used to include or exclude matches.

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.