How to Use the AND and OR Logical Functions in Google Sheets

Testing multiple conditions to return TRUE or FALSE results

Test Multiple Conditions with Google Spreadsheets AND/OR Functions

Screenshot by Ted French

The AND and OR functions are two of the better-known logical functions in Google Sheets. They test to see whether the output from two or more target cells meets conditions that you specify.

These logical functions will only return one of two results (or Boolean values) in the cell where they are used, either TRUE or FALSE:

  • For the AND Function, formulas in multiple cells are tested. Only if all of these formulas are true will the function return a TRUE response. If not, the function returns FALSE as a value.
  • For the OR function, if any one of the tested formulas is true, then the OR function returns an answer of TRUE. Only if all formulas are not true will OR give you a FALSE value in the cell where it is located.

These TRUE or FALSE answers for the AND and OR functions can be displayed as is in the cells where the functions are located, or the functions may be combined with other Google Spreadsheet functions, such as the IF function, to display a variety of results or to carry out a number of calculations.

How the Logical Functions Work in Google Sheets

The image above, cells B2 and B3 contain an AND and OR function, respectively. Both use a number of comparison operators to test a variety of conditions for the data in cells A2, A3, and A4 of the worksheet.

The two functions are:

=AND(A2<50,A3<>75,A4>=100)
=OR(A2<50,A3<>75,A4>=100)

Tthe conditions they test are:

  • If the data in cell A2 is less than 50 (< is the symbol for less than)
  • If the data in cell A3 is not equal to 75 (<> is the symbol for not equal to)
  • If the data in cell A4 is greater than or equal to 100 (>= is the symbol for greater than or equal to)

For the AND function in cell B2, the data in cells A2 to A4 must match all three of the conditions above for the function to return a TRUE response. As it stands, the first two conditions are met, but since the value in cell A4 is not greater than or equal to 100, the output for the AND function is FALSE.

In the case of the OR function in cell B3, only one of the conditions above needs to be met by the data in cells A2, A3, or A4 for the function to return a TRUE response. In this example, the data in cells A2 and A3 both meet the required condition, so the output for the OR function is TRUE.

Syntax and Arguments for AND/OR Functions

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the AND function is:

=AND (logical_expression1, logical_expression2, ...)

The syntax for the OR function is:

=OR (logical_expression1, logical_expression2, logical_expression3, ... )
  • logical_expression1 [Required] refers to the condition being tested. The form of the condition is normally the cell reference of the data being checked followed by the condition itself, such as A2 < 50.
  • logical_expression2, logical_expression3, ...  [Optional] are additional conditions that can be tested.

Entering the AND Function

The following steps cover how to enter the AND function located in cell B2 in the image above. The same steps can be used for entering the OR function located in cell B3.

Google Sheets does not use dialog boxes to enter a function's arguments the way Excel does. Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell.

  1. Click on cell B2 to make it the active cell; this is where the AND function is entered and where the function's result will be displayed.
  2. Type the equal sign (=) followed by the function AND.
  3. As you type, the auto-suggest box appears with the names of functions that begin with the letter A.
  4. When the function AND appears in the box, click on the name with the mouse pointer.

Entering the Function Arguments

The arguments for the AND function are entered after the open parenthesis. As in Excel, a comma is inserted between the function's arguments to act as a separator.

  1. Click on cell A2 in the worksheet to enter this cell reference as the logical_expression1 argument.
  2. Type < 50 after the cell reference.
  3. Type a comma after the cell reference to act as a separator between the function's arguments.
  4. Click on cell A3 in the worksheet to enter this cell reference as the logical_expression2 argument.
  5. Type <> 75 after the cell reference.
  6. Type a second comma to act as another separator.
  7. Click on cell A4 in the worksheet to enter the third cell reference.
  8. Type >=100 after the third cell reference.
  9. Press the Enter key on the keyboard to enter the closing parenthesis after the arguments and to complete the function.

The value FALSE should appear in cell B2 because the data in cell A4 does not meet the condition of being greater than or equal to 100.

When you click on cell B2, the complete function

=AND(A2<50,A3<>75,A4>=100) 
appears in the formula bar above the worksheet.

OR Instead of AND

The steps above may also be used for entering the OR function located in cell B3 in the worksheet image above.

The completed OR function would be:

=OR(A2<50,A3<>75,A4>=100)

A value of TRUE should be present in cell B3 since only one of the conditions being tested needs to be true for the OR function to return a TRUE value, and in this example two of the conditions are true:

  • The data in cell A2 is less than 50.
  • The data in cell A3 does not equal 75.