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

Get FREE Advanced Excel Exercises with Solutions!

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.

and function in excel overview

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

and function syntax

  • Function Objective:

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

  • Syntax:

=AND(logical1, [logical2])

  • Arguments Explanation:
Argument Compulsory/Optional Explanation
logical1 Compulsory 1st logical condition.
[logical2] Optional 2nd logical condition.
  • 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.

and function for logical test in excel

📌 Steps:

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

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

➤ 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 for logical test in excel

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.

and function with conditional formatting in excel

📌 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.

and function with conditional formatting in excel

📌 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.

and function with conditional formatting in excel

📌 Step 4:

➤ Press OK and you’re done.

and function with conditional formatting in excel

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

and function with conditional formatting in excel

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:

=OR(AND(E5=$J$11,F5<$J$13),AND(E5=$J$12,F5<$J$13))

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

and function with or function in excel


Similar Readings


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:

=IF(AND(D5>$J$11,F5<$J$12),C5,"")

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

and function with if in excel

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:

=AND(D5>MIN(B5:C5),D5<MAX(B5:C5))

➤ 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.

and function with max min in excel

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

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo