How to use the Google Sheets AND function

The AND function in Google Sheets accepts logical expressions as input arguments, then evaluates them as TRUE or FALSE.

If all of the input arguments evaluate to TRUE, then the formula returns TRUE as the end result. However, if any of the input arguments evaluate to FALSE, the formula returns FALSE as an output.

In other words, all logical expressions need to be true for AND to return TRUE.

Spreadsheets are just the start

AND function Syntax

=AND(logical_expression1, [logical_expression2, …])

  • logical_expression1 – an expression that evaluates as TRUE or FALSE. IT can also be a reference to the cell that contains a logical expression.
  • [logical_expression2, …] – these are optional expressions for the function to evaluate.

How to use the AND function

Let’s try various combinations of input arguments within the AND formula and examine the results.

google sheets AND formula 1

You may have already guessed that the first two examples, each with a single expression, aren’t that useful to us.

It is interesting to note that the AND function works even with numbers as input. In this case, it simply evaluates them as FALSE if they are zero or TRUE for any other value. Check out the examples below.

google sheets AND formula 2

The AND function also takes a range of cells as an input, as shown below.

google sheets AND formula 3

While it is hard to deny the utility of the AND formula in the real world, it has limited applications as a standalone function.

However, when used in conjunction with other formulas like IF, we can see its magic coming to life. In the examples below, look at the first and third formulas. The second and fourth formulas are their alternatives. Notice how simple it gets when we use AND instead of multi-level IF formula nesting.

google sheets AND formula 4

Combining AND and FILTER functions

You can combine FILTER and AND to extract data if it meets multiple conditions.

=FILTER(A:C, AND(A:A > 50, B:B <100))

This formula filters the range A1:C10 to include only the rows where the values in column A are greater than 50 and the values in column B are less than 100.

AND function with conditional formatting

Usually you can only set one condition to apply a certain formatting. But with AND you can apply multiple criteria.

Just follow these simple steps:

  1. Select the range you want to format.
  2. Go to Format > Conditional formatting.
  3. In the Custom formula is field.
  4. Insert the ANDfunction so the condition includes two values.
  5. Choose the formatting style and click Done.

AND function

The AND function in Google Sheets is a versatile tool for evaluating multiple conditions in your data analysis.

By combining it with other features you can create powerful and complex formulas to meet your specific needs.

If you want a function that evaluates to TRUE if any of the conditions are met, then you need to learn about the OR function.

Ready to streamline your spreadsheet data?

You may also like…