AND Function – Explained with Examples

The AND function is a one of Excel’s logical functions, and is used to test if all conditions are TRUE, or (conversely) if any conditions are FALSE.

The AND function:

  • returns TRUE if all arguments are TRUE, or all arguments evaluate to TRUE
  • returns FALSE if any one or more arguments evaluates to FALSE or are FALSE

Function Syntax

=AND(logical_test [,logical_test])
  • requires at least one logical argument
  • arguments must evaluate to TRUE or FALSE
    • including any non-zero (positive or negative) number (TRUE) or 0 (FALSE)
  • the AND function can contain a maximum of 256 arguments (i.e. the first mandatory argument and 255 options arguments).

Usage

The AND function is not usually used in isolation but is often combine with other functions which use logical inputs. For examples AND is often used with the IF function.

The AND function can be combined with other logical operators such as OR, NOT, XOR etc. to create more complex logical tests.

NOTE: If any of the arguments does not return a logical value (TRUE or FALSE) then the AND function will returns #VALUE! error

Examples

Evaluating to TRUE

The AND function will output TRUE where all arguments are TRUE or equate to TRUE.

Where all arguments are TRUE

=AND(TRUE, TRUE)

or where all arguments equate to TRUE as follows

=AND(1, TRUE, 0 = 0, 1 < 2, "hello" = "hello")

In both cases all values are TRUE or equate to TRUE, including the comparison of strings, which outputs the TRUE value

TRUE

NOTE: that the text evaluation is NOT case sensitive

=AND("HELLO" = "hello")

will still output

TRUE

Evaluating to FALSE

Any value equating to FALSE will result in the AND value outputting FALSE

=AND(TRUE, TRUE, TRUE, TRUE, FALSE)
FALSE

Using AND with other functions

The most obvious use of AND is in conjunction with the IF function.

The IF function accepts a logical test and outputs separate values for TRUE or FALSE logical tests.

AND extends the logical test to test more than one test. Additionally AND can be combined with NOT and OR and XOR to produced more complex tests.

Example using AND function – with other functions (IF & OR)

We have been asked to evaluate some data.

We are to assign each row a PASS if all the following are TRUE

  • the Color is BLUE or RED,
  • the Star (rating) is at lease 3 stars
  • the Cost was less than 9000
  • and the Value shows no loss (compared to the cost)

The data could be part of a larger dataset and so to avoid error and for speed we will create a formula to evaluate the data. The formula would be the same for each data row.

Since all tests need to be TRUE for the output to be a PASS we will use the AND function with all the tests.

We can create discrete functions for each logical test as arguments to the AND function, The AND function will only output TRUE if all the logical test are TRUE. The IF function will take the result of the AND evaluation of the tests, and return PASS if all tests are TRUE, otherwise output FAIL.

For the individual tests:

  1. For the color we will use the OR function to test for both colors – remembering that text comparison is not case sensitive.
  2. We can count the stars with the LEN function, “at least 3 stars” as each star is a character length or 1
  3. We compare the cost value against the limit of under 9000
  4. We compare the Value to the Cost (if they are equal or greater, that is ok)

Below we can see a partial evaluation of the arguments in column G, before the AND function returns its results.

Column H returns the final result.

We find in our dataset there is only one row, the final row of data which satisfies all tests and returns TRUE and outputs a PASS.

Related Topics

F011