Have you ever struggled to organize and clean up data in Google Sheets? Well, look no further! The AND function in Google Sheets is here to save the day. This powerful logical function returns TRUE if all the conditions are met, and FALSE otherwise. It’s a handy tool that can be used in combination with other functions like IF and OR to categorize and arrange data.
An Example of the AND Formula
Let’s say we have the following dataset:
Our goal is to find all the rows that contain “Vendor” in column A and values less than $500,000 in column B. To illustrate this, I’ve highlighted these rows in yellow.
The first test expression, in cell C2, is:
=A2="Vendor"
And the second test expression, in cell D2, is:
=B2<500000
The results of these two test expressions can then be used in an AND formula:
=AND(C2,D2)
or
=C2&D2
All these AND formulas will produce the same result. You can enter ranges or individual data cells.
We can also directly insert the two expressions into the AND function like this:
=AND(A2="Vendor",B2<500000)
These AND formulas will only return TRUE when both conditions are met; otherwise, the result will be FALSE.
The yellow rows all have “Vendor” in column A and a value less than $500,000 in column B.
Syntax of the AND Function
The AND function takes one or more arguments, which can be cells, ranges, or logical test expressions. It is part of the logical functions family in Google Sheets, along with the OR function.
Notes on the Function
Let’s take a closer look at the inputs for the AND function:
Expressions
Expressions are tests that return TRUE or FALSE values.
For example, here are some expressions that return TRUE or FALSE results and can be inserted into an AND formula.
These expressions can be in their own cells and referenced in the AND formula like this:
=AND(A1="Yes",B1>10)
Or they can be directly inserted into AND like this:
=AND(A1="Yes",B1>10)
Numeric Inputs
The number 0 is interpreted as FALSE by the AND function. All other numbers are interpreted as TRUE.
So, AND(1,1) returns TRUE. But AND(1,0) returns FALSE.
Text Inputs
Text inputs are generally ignored by the AND function, although there is a bit of subtlety to it. Check out the Twitter thread at the bottom of this article for more details.
AND + IF Function
The AND function pairs perfectly with the IF function. The TRUE/FALSE output of the AND formula is directly used as the first argument of the IF function.
Using the same example as before, let’s say we want to label vendors whose amount is less than $500,000 in our dataset.
Simply place the earlier AND formula in the first argument of our IF function:
=IF(AND(A2="Vendor",B2<500000), "Vendor < 500k", "Other")
The result of this formula is:
Advanced Uses
AND is a fantastic function to have in your function repertoire. Once you know how to use it, you’ll discover all sorts of useful and interesting applications for your work.
For example, I’ve used a nested AND in some of the formulas in my Etch A Sheet game (a Google Sheets version of Etch A Sketch).
A Note About the AND Function with Text Inputs
Now, this might be a little bit of a rabbit hole, as it’s unlikely that you’ll be entering a text value into the AND function.
But if you’re curious about different formula errors and how AND handles text values, check out this Twitter thread:
“The problem with being curious is that you keep digging deeper and deeper into the rabbit hole. You feel compelled to do it.
Consider the AND function in Google Sheets…
Simple, right?
Not quite, it has some strange behavior with text inputs.”
