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.
- Arguments 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.
➤ 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.
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:
➤ 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.
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.
➤ Select the output Cell G5 and type:
➤ Press Enter, fill down the entire column and you’ll get the results at once.
- How to Use Excel SWITCH Function (5 Examples)
- Use Excel XOR Function (5 Suitable Examples)
- How to Use IFNA Function in Excel (2 Examples)
- Nested IF and AND Functions in Excel (4 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.
➤ 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.
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.
➤ 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.
💡 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.
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.