The AND function in Google Sheets is useful when you are working with logical functions that you’re trying to test multiple arguments.
Table of Contents
The AND function does this by supplying its attributes to make it work nicely.
The use of the
AND function is quite effortless. It makes more sense, especially when dealing a large data where manual counter checking isn’t possible. To make this function work smoothly, you only have to provide logical expression/s and be mindful of what comparison operators to use. As we dive deeper into this guide, we will learn the definition of terminologies, its use, and how to work with the AND function in Google Sheets.
The use of the
AND function is almost similar to the
OR function. Except that, with
OR, it will only check if any of the tested expressions are true. If there’s even one that’s true, it will return TRUE. While, with
AND, it will check if all expressions are true. If all are true, then that’s the only time it will return TRUE.
Let’s take the same example as what we had in our previous How to Use OR Function in Google Sheets guide so you can clearly see the difference.
Say you are a sales manager that has 20 sales agents. You would want to check if all of them reached the sales quota.
In our example above, we wanted to see if ALL the sales agents have met the sales quota. In this particular situation, when we make use of the
OR function, it will only check if there’s even one agent who has met the quota. But, when we make use of the AND function, which is more appropriate at this point, then it would check each and every agent and will return TRUE if it sees that all of them have met the requirement.
Let’s say their sales quota is 450 units. Using the
AND function, you can have your agents’ sales followed by a comparison operation, which is, in this case, an equal sign ‘=’ equate to 450 units.
Hit on the ‘Enter’ key, and it will give you a TRUE or FALSE answer. Remember, the
AND function will check each sales agent and will return TRUE if all of them met the condition.
You’ll better appreciate the
AND function if you nest it with other logical functions, such as
The Anatomy of the AND Function
So the syntax (or how we write) the
AND function is as follows:
Here’s how it will look like:
=AND(logical_expression1, [logical_expression2, ...])
Let’s break this down to better understand how this formula works:
=every function in Google Sheets starts with an equal sign.
AND()this is our function. We need to add the
logical_expression1attribute for it to work properly in Google Sheets.
logical_expression1is a required attribute as this refers to the condition that is being tested. We use comparison operators (
<(less than), and
=(equal), to help form the condition (e.g. A1 < 10, which means the cell A1 is less than 10).
logical_expression3and etc. are optional additional conditions which you may add should you need to evaluate them.
At this point, this may sound easy or confusing to you, but we want to make sure that you fully understand how the AND function works in Google Sheets. So, we’ll have a real example below and walk you through the process.
A Real Example of Using AND Function
Have a look at the example below to see how the AND function is used in Google Sheets.
In the example above, we have ten sales agents with names A, B, C, up to J. Each sales agent has his or her own sale in units. For this guide, we will only be testing if the five sales agents A to E have reached the sales quota for a specific month.
Here’s what this example does:
- We have actively selected D5, and we want to use the
ANDfunction to check if agents A to E have met the 450 unit quota.
- Since we are checking the sales of agents A to E, we now know that we have five logical expressions to add.
- After identifying how many logical expressions we should have in our formula, we then created each of them.
- In this example, we want to check if sales agents A to E has reached the 450 unit quota. In that case, our expression is greater or equal to 450. So, we used the >= sign.
- For the first logical expression, we had B2>=450. The second logical expression is B3>=450. We continued the logical expressions up to B6, for the sale of agent E.
- We ended the formula with a close parenthesis ‘)’ before we got the final result, FALSE.
- At this point, you may wonder why we got FALSE as the answer. It’s merely because agent E didn’t meet the quota. His sale in units is only 300.
You can try it by yourself by making a copy of the spreadsheet using the link I have attached below:
Let’s begin writing our own AND function in Google Sheets.
How to Use AND Function in Google Sheets
- Firstly, click on an empty cell to make it active. This is where we will write our working formula. For this guide, I have selected cell D5.
- Since we already know how many logical expressions should we have, we now move forward to writing our function. In this step, we will start with an equal sign (
=), followed by our function,
ANDand our opening parenthesis ‘(‘. You will notice that there’s a pop-up message that would appear. This will serve as our extra guide in writing our formula.
- Next, we will now add our logical expressions. We start off by writing B2>=450, then B3>=450, and so on until you reach B6.
- End the formula with a closing parenthesis ‘)‘.
- Now, hit on the ‘Enter’ key. Voila! The final result of FALSE will be outputted based on the outcome of the logical expression we have given.
That’s pretty much it. You can now use the
AND function together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂