How to Use AND Function in Excel (5 Suitable Examples)

In Microsoft Excel, AND is one of the most frequently used functions and the AND function is applied to check for logical tests under different conditions. In this article, you’ll get to learn how you can use this AND function efficiently with different conditions in Excel.

The above screenshot is an overview of the article which represents an application of the AND function in Excel. You’ll learn more about the dataset as well as the methods to use AND function properly in the following sections of this article.

Download Practice Workbook

You can download our Excel Workbook that we’ve used to prepare this article.

Introduction to the AND Function

• Function Objective:

Checks whether all the arguments are TRUE, and returns TRUE if all arguments are TRUE.

• Syntax:

=AND(logical1, [logical2])

• Arguments Explanation:

• Return Parameter:

Returns with a logical value- TRUE or FALSE.

5 Suitable Instances of Using AND Function in Excel

1. Using AND Function to Test with Logical Values

Let’s get introduced to our dataset first that combines a range of data for a charity foundation. Columns C, D, E, and F consist of the donor names, donation amounts, mediums of donations and donation dates respectively. In Column G, by using AND function, we’ll find out the rows where the donors have donated more than $500 before August, 2021.

📌 Steps:

➤ In the output Cell G5, the formula will be:

➤ Press Enter and the function will return TRUE that means Adam has donated more than $500 before 1 August, 2021.

➤ Now use Fill Handle to autofill the rest of the cells in Column G.

AND function returns with FALSE logical value if any one of the conditions appears FALSE. The function will return TRUE only if all conditions are TRUE.

Read More: How to Return TRUE or FALSE Using Excel AND Function

2. Using AND Function with Conditional Formatting

Now we’ll find out the names who have donated more than $500 before 1 August, 2021 by highlighting those names with a color. So, we have to assign a formula and a color in Conditional Formatting for the output data.

📌 Step 1:

➤ Select all the names in Column C.

➤ Under Home tab, choose New Rule command from the Conditional Formatting drop-down. A dialogue box will open.

📌 Step 2:

➤ Select “Use a formula to determine which cells to format” as Rule Type,

➤ In the formula box, type:

=AND(D5>$I$11,F5<$I$12)

➤ Press Format and another dialogue box will appear.

📌 Step 3:

➤ From the Fill option, select a color that you want to see on the output cells in your dataset or table.

➤ Press OK and a preview will be shown in the New Formatting Rule dialogue box.

📌 Step 4:

➤ Press OK and you’re done.

Like the screenshot below, you’ll find the donor names with the selected color who have donated more than $500 before 1 August, 2021.

Read More: How to Use Conditional Formatting with AND Function in Excel

3. Combining OR with AND Function

OR function returns with TRUE if any of the conditions found TRUE and returns FALSE if all conditions appear to be FALSE. So, it’s completely opposite to the AND function. By combining OR with AND function, we can add multiple criteria from our dataset. Assuming that we want to find in Column G how many rows contain the donors who have donated through cash or cheque before 1 August, 2021.

📌 Steps:

➤ Select the output Cell G5 and type:

➤ Press Enter, fill down the entire column and you’ll get the results at once.

Similar Readings

• How to Use Excel SWITCH Function (5 Examples)
• Nested IF and AND Functions in Excel (4 Suitable Examples)
• Use Excel XOR Function (5 Suitable Examples)
• How to Use Excel IFNA Function (5 Suitable Examples)

4. Combining IF with AND Function

By using IF function before AND function, we can add some statements based on the return types of logical values. For example, we want to see the names only in Column G who have donated more than $500 before 1 August, 2021.

📌 Steps:

➤ In the output Cell G5, the related formula will be:

➤ Press Enter, autofill the entire column and you’re done. You’ll see all the names of the donors based on the selected criteria.

Read More: How to Use IF with AND Function in Excel (Formula + VBA)

5. Enclosing MIN & MAX Functions with AND

By using MIN and MAX functions inside AND function, we can determine if a value falls between two other values. For example, in our new dataset, Column B and Column C have some experimental results found in a laboratory with iterations. Column D consists of the final results that have to be found between the other two values in the same rows.

📌 Steps:

➤ In Cell E5, the related formula will be:

➤ Press Enter and you’ll find all the return values with TRUE or FALSE. So, if the final data is found between the other two values, then the function will return TRUE, otherwise FALSE.

Read More: How to Use IFS and AND Functions Together in Excel (3 Examples)

💡 Things to Keep in Mind

🔺 AND function is able to include up to 255 logical conditions at the same time.

🔺 The function will appear TRUE only if all conditions are TRUE, if any one of the conditions does not meet the requirement, the function will return FALSE.

🔺 The possible best outcome of using the AND function is when you need to add multiple conditions for other functions.

Concluding Words

I hope all of the methods mentioned above to use the AND function will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Related Articles

• How to Use IFS Function in Excel (3 Examples)
  
• Use SUMIF and AND Function in Excel (2 Easy Methods)
• How to Use TRUE Function in Excel (10 Ideal Examples)
  
• Use AND Function in Excel with Text (8 Easy Examples)
• How to Use FALSE Function in Excel (7 Easy Examples)
• Use AND and OR Functions in Excel (3 Practical Examples)