How to Use the AND Function in Excel – 7 Examples

This is an overview.

Introduction to the AND Function

• Function Objective:

Checks all arguments and returns TRUE if all arguments are TRUE.

• Syntax:

=AND(logical1, [logical2])

• Arguments Explanation:

• Return Parameter:

Returns a logical value- TRUE or FALSE.

Example 1 – Using the AND Function to Test with Logical Values

The sample dataset showcases donor names, donation amounts, mediums of donations and donation dates. The AND function will be used in Column G , to find  rows containing more than $500 before August, 2021.

Steps:

•  In G5, enter the formula:

• Press Enter. The function will return TRUE: Adam donated more than $500 before 1 August, 2021.
• Drag down the Fill Handle to see the result in the rest of the cells.

The AND function returns FALSE if a condition isn’t met.

Example 2 – Using the AND Function with Conditional Formatting

To highlight names for donations greater than $500 before 1 August, 2021, use Conditional Formatting:

Step 1:

• Select all names in Column C.
• In the Home tab, choose New Rule in Conditional Formatting.

Step 2:

• Select “Use a formula to determine which cells to format” as Rule Type,
• Enter:

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

• Click Format.

Step 3:

• In Fill, select a color.
• Click OK to see a preview.

Step 4:

• Click OK.

This is the output.

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

Example 3 – Combining the OR with the AND Function

The OR Function returns TRUE if any of the conditions are met and FALSE if all conditions aren’t met.

To find names for cash or check donations before 1 August, 2021 in Column G:

Steps:

• In G5, enter:

• Press Enter
• Drag down the Fill Handle to see the result in the rest of the cells..

Example 4 – Combining the IF with the AND Function

Use the IF function before the AND function.

To see the names who donated more than $500 before 1 August, 2021 in Column G .

Steps:

• In G5, enter the formula:

• Press Enter.
• Drag down the Fill Handle to see the result in the rest of the cells.

Read More: How to Use IFS and AND Functions Together in Excel

Example 5 – Enclosing the MIN & MAX Functions with AND

Use the MIN and MAX functions inside the AND function to determine if a value falls between two other values.

Steps:

• In E5, enter the formula:

• Press Enter.

If the output is found between the two values, the function returns TRUE. Otherwise FALSE.

Example 6 – Applying a Nested AND Function in Excel

To check multiple conditions, use the nested AND function.

Find online donated amounts greater than $300, between 7/15/2021 and 8/1/2021.

• Use the following formula.

Example 7 – Using the AND Function with a Nested IF Function

Use the AND function with the nested IF function.

Categorize the donors based on the donated amount.

• Use the following formula.

Read More: Nested IF and AND Functions in Excel 

Things to Keep in Mind

The AND function is able to include up to 255 logical conditions.

Frequently Asked Questions

•  What happens if I use an empty cell as an argument in the AND function?

The empty cell is considered a logical value of FALSE. You can use the IF function with the ISBLANK function to avoid this issue.

Download Practice Workbook

Download the Excel Workbook.

Excel AND Function: Knowledge Hub

• How to Return TRUE or FALSE Using Excel AND Function
• How to Use AND Function in Excel with Text
• How to Use SUMIF and AND Function in Excel

<< Go Back to Excel Functions | Learn Excel