Excel IF formula with AND logical function

This post will guide you how to use IF function combining with AND logical function in Excel.

If you want to check if a value in cell match two or more conditions at the same time, if the test is TRUE, then you can take certain action. You can use the IF function combining with AND function to construct a condition statement. If the test is FALSE, then take another action.

Excel formula using IF & AND function

The syntax of AND function in excel is as follow:

=AND(condition1,[condition2],...)

The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.

Now we want to check the results of two exam scores where the first score in cell A1 is greater than or equal to 60 AND the second score in cell B1 is greater than or equal to 80. If both conditions are TRUE, then returns the text of “good”, and if FALSE returns “bad”. Based on the above logic, we can write down the below IF formula:

=IF(AND(A1=60,B1=80),"good","bad")

We can enter the above formula into cell D1, and then drag the Fill Handle to the range D1:D3.it will apply for the other cells for this formula.

Excel formula using IF AND function

The logic test will check the two different cells in the above IF formula, if we can run two or more tests on the same cell. Of course yes, for example, if you want to check cell A1 if the value in cell A1 is greater than 60 but less than or equal to 70. If the test is TRUE, then take one action.

The logical test we can use for above logic as below:

=AND(A1>60, A1<=70)

IF formula:

=IF(AND(A1>60,A1<=70),”good”,””)

Excel formula using IF AND function2

In this formula, if the test is FALSE, we just add an empty string (“”), IF the test is TRUE, the test string of “good” will appear.

Note: The AND function will check all test conditions in excel, even if one of the already tested conditions has a value of FALSE. This behavior is different with other programming languages. If any previous test conditions return FALSE, the subsequent conditions will not be tested.

For example: If the value of Cell B1 is 0, then the below IF&AND formula will return error message as “Divide by Zero Error”.

=IF(AND(B1<>0,(1/B1)>0.2),”A”,”D”))

Excel formula using IF AND function3

As mentioned above, the AND function will check all conditions. To avoid this issue, we can use a nested IF function and check if the value of Cell B1 is not equal to 0 in the first IF statement.

The nested if formula is as below:

=IF(B1<>0,IF((1/B1>0.2),”A”,”D”))

Related Formulas

  • Excel IF formula with equal to logical operator
    The “Equal to” logical operator can be used to compare the below data types, such as: text string, numbers, dates, Booleans.  This section will guide you how to use “equal to” logical operator in excel IF formula with text string value and dates value…
  • Excel IF formula with OR logical function
    If you want to check if one of several conditions is met in your excel workbook, if the test is TRUE, then you can take certain action. You can use the IF function combining with OR function to construct a condition statement…
  • Excel IF formula with AND & OR logical functions
    If you want to test the result of cells based on several sets of multiple test conditions, you can use the IF function with the AND and OR functions at a time…

Related Functions

  • Excel AND function
    The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…) …
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE….
  • Excel nested if function
    The nested IF function is formed by multiple if statements within one Excel if function. This excel nested if statement makes it possible for a single formula to take multiple actions…

 

Related Posts
Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

How to Average Only Positive or Negative Numbers of a Range

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative ...

VLOOKUP with Two Lookup Tables

VLOOKUP is one of the key functions among all lookup & reference functions in Excel. Today we will show you the application of VLOOKUP function when there are two lookup tables. EXAMPLE Table1 and table2 record the rates of Y2020 ...

Approximate Match with Multiple Criteria by INDEX & MATCH
Approximate Match with Multiple Criteria by INDEX & MATCH 1 & 2

In Excel, INDEX function and MATCH function are often used together for returning data from specific position. And MATCH function is one of Excel lookup & reference functions that can return approximate value by setting match type. Above all, through ...

How to Statistic Pass 4 Out of 5 Rounds in a Competition by Excel Functions
Statistic Pass 4 Out of 5 Rounds 1

Suppose you are counting the numbers of participants who are the winners in a competition. And the passing line is provided as pass 4 out of 5 rounds of games. You need to know if participant pass the game or ...

Average the Last N Numeric Values in Excel
Average the Last N Numeric Values 7

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last ...

How to Average and Ignore Errors in Excel
average and ignore errors2

This post will guide you how to average a list of values and ignoring any errors in the given range in Excel 2013/2016 or Excel office 365. You can use the AVERAGEIF function or AGGREGATE function to calculate the average ...

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column
How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column14

Sometimes we may meet the case that subtotal values for different groups and only record a subtotal value for one group in a column, for other cells in this group, keep them blank. Today we will introduce you how can ...

Running Count of Occurrence List
running count of occurrentce in list2

This post will guide you how to get running count of an occurrence in a list with formula in Excel 2013/2016 or Excel office 365. How to create a running count of certain values that appear in the given range ...

How to Count Unique Numeric Values with Criteria in a Range in Excel
count unique numeric values criteria9

We have talked that how to count unique numeric values in a data set in Excel in the previous post. And this post will guide you how to count unique numeric values with criteria in the range in Excel 2013/2016 ...

Sidebar