AND

What does this function do?

The AND function checks whether all provided conditions are TRUE. Returns TRUE only if every condition is met, FALSE if any condition is not met.

It can be used to verify that a series of numbers meets specific conditions, or to check that a number or date falls between upper and lower limits.

Like the OR function, it’s typically used in combination with other logical functions, most commonly with the IF function, and with array formulas.

Syntax

=AND(logical1, [logical2], ...)

=AND(value_range)

Formatting

The function correctly processes:

• Numbers and calculations returning numbers
• Logical expressions (TRUE, FALSE) and calculations returning them
• Cells containing these values
• Cell ranges where at least one cell contains numbers or logical values
• Arrays of values or calculations

If a range containing text values is provided, they are ignored by the function, as are empty cells.

The function returns an error if:

• only text or empty cells are provided as input (one or more)
• there are errors among the values (#VALUE!, #N/A, #NAME?, #NULL!, #DIV/0!, #REF!, #NUM!)

Usage examples

Example 1

The following example shows a list of test results.

The teacher wants to find students who scored above average on all three exams.

The AND function was used to verify that the score for each subject is above average.

The result TRUE is shown for students who scored above average in all three exams.

=AND(B2>=AVERAGE($B$2:$B$11),C2>=AVERAGE($C$2:$C$11),D2>=AVERAGE($D$2:$D$11))

Example 2

Parents are choosing a girl’s name and want the name to contain all three soft sounds.

Excel doesn’t allow filtering rows by more than two criteria, but for this task we can use an array formula with the AND function in combination with the SEARCH function and ISNUMBER.

Note that for the function to check for the presence of all letters, this must be an array formula. It should be entered without curly braces, but the cell entry should be completed using Ctrl + Shift + Enter.

{=AND(ISNUMBER(SEARCH(C$2:C$4,$A11)))}

The exact formula is highlighted in the screenshot. It determines whether the name contains all three letters:

In column H, the OR function was useful – it passes names if they contain TRUE for at least one of the letter sets.

Other Logical functions in Excel

IF, OR, NOT, TRUE, FALSE, IFERROR, IFS, SWITCH, XOR

This post is also available in RU.