What Is AND Function In Google Sheets?
The AND function in Google Sheets is a logical function that returns TRUE or FLASE values based on two or more conditions. It returns TRUE if all the logical values or conditions are true.
Since the Google Sheets AND function returns logical values, we can combine it with other functions such as, IF(), OR(), etc. with single or multiple conditions. For example, the below table shows the performance of employees with their achieved targets.
Select the cell C2, enter the formula =AND(B2>=50), press the “Enter” key and once we get the result, drag the formula from cell C2 to C6 using the fill handle.
Table of Contents
Key Takeaways
- The AND function in Google Sheets checks two or more conditions and returns TRUE if all the conditions are TRUE, else it returns FALSE.
- The formula has one mandatory argument that refers to the first logical value to be tested and other optional arguments that denote the second or the corresponding logical values and so on.
- The function reads operators such as equal to, not equal to, greater than, less than, greater than or equal to, lesser than or equal to, etc.
- The function ignores blank values and returns #VALUE! error if the arguments have empty cells.
AND( ) Google Sheets Formula
The syntax of the AND Google Sheets Formula is:
The arguments of the AND Google Sheets Formula are:
- logical_expression1: It is a mandatory argument and refers to the first logical value to be tested.
- [logical_expression2, logical_expression3, and …]: It is an optional argument that denotes the second or the corresponding logical value.
Operators Used In The AND Function
The AND in Google Sheets involves some operators to perform certain tasks. The names and symbols of the operators are shown in the table below:
| Operators | Names |
| = | Equal to |
| <> | Not Equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Lesser than or equal to |
How To Use AND In Google Sheets?
We can use the AND In Google Sheets in 2 ways, namely,
- Access from the Google Sheets ribbon.
- Enter in the worksheet manually.
Method #1 – Access From the Google Sheets Ribbon 🡢
Choose an empty cell for the output à select the “Insert” tab à click the “Function” option right arrow à click the “Logical” option right arrow à select the “AND” function, as shown below.
The “AND” formulaappears, as shown below. Enter the argument as the cell reference.
Method #2 – Enter in the Worksheet Manually 🡢
- Select an empty cell for the output.
- Type =AND( in the cell. [Alternatively, type =A or =AND and double-click the AND function from the Google Sheets suggestions.]
- Enter the arguments as cell values or cell references and close the brackets.
- Press Enter to view the outcome.
Examples
Let us understand how the AND function in Google Sheets works with some examples.
Example #1 – Test Multiple Conditions Using AND Function
Consider the below table listing the students’ scores in columns, A and B, respectively. We will test multiple conditions such as, whether the students have scored greater than or equal to 60 or not, also if the score is less than 80.
The steps to obtain the results for multiple conditions using the AND function are as follows:
Step 1: Select cell C2 and enter the formula =AND(B2>=60
Step 2: Let us enter the second condition, whether the entered values are less than 80. Therefore, type B2<80 next to the first argument, as shown below. The formula now is =AND(B2>=60,B2<80
Step 3: Close the brackets and press Enter key. The AND function has returned the value as TRUE in cell C2.
Step 4: Drag the formula from cell C2 to C6 using the fill handle.
The AND function has found that cells B2, B3 and B5 satisfy both the conditions.
Example #2 – AND + OR Function 🡢
The below table shows the number of available bedsheets (double and single) in the store. We will check for multiple conditions using the OR and the AND function in Google Sheets.
In the table,
- Column A shows the total number of bedsheets.
- Column B displays the available double bedsheets.
- Column C displays the available single bedsheets.
- The results obtained should be displayed in column D.
The steps to obtain results using OR and AND in Google Sheets are as follows:
Step 1: Select cell D2 and enter the formula =AND(A2>=50,
Step 2: We will also check whether the entered value in column B is greater than or equal to ‘20’ and the value in column C is greater than or equal to ‘15’ using OR function.
So, type OR(B2>=20),OR(C2>=15) in the formula and close the AND function with the bracket.
The complete formula is =AND(A2>=50,OR(B2>=20),OR(C2>=15)).
If both the conditions are true, OR with AND function returns TRUE; if the data does not satisfy the conditions, it returns FALSE.
Step 3: Press Enter key. The OR with AND function has returned the value as FALSE in cell D2, as shown below.
Step 4: Drag the formula from cell D2 to D6 using the fill handle. The OR with AND functions has returned the availability status using the conditions, >=20 and >=15, to get the output shown below.
Example #3 – AND + IF Function 🡢
The following table lists fruits along with the stock and demand details. We need to find the fruits with and without sales using IF and the AND function in Google Sheets.
In the table,
- Column A displays the fruits.
- Column B shows the stock.
- Column C indicates the demand for the fruits.
- The results obtained should be displayed in column D.
The steps to obtain results using the IF and the AND function in Google Sheets are as follows:
Step 1: Select cell D2 and enter the formula =IF(AND
Step 2: We should select the cell with the first value (first argument), i.e., cell B2.
Step 3: Let us check whether the entered values are greater than or equal to 30 (>=30).
So, type >=30 next to the first argument.
Step 4: We need to select the corresponding second value (second argument), i.e., cell C2. We will also check whether the data is high in demand or not. High demand indicates that the fruit can be sold easily. So, type = “High” in the formula and close the AND function with the bracket.
The IF function returns the value with TRUE or FALSE conditions. So, in this example, if both the conditions are TRUE, then the function returns “Sale” and returns the output as “No Sale” when the conditions are FALSE.
The complete formula is =IF(AND(B2>=30,C2=“High”),“Sale”,“No Sale”)
Step 5: Close the brackets and press Enter key to get the output, as Sale, as shown below.
Step 6: Drag the formula from cell D2 to D6 using the fill handle.
The AND with IF Google Sheets functions have found that bananas have a high demand value and satisfies the condition, >=30. Also, the TRUE or FALSE conditions are indicated using ‘Sale’ and ‘No Sale’ conditions.
Example #4 – Find Output Using Operators 🡢
The below table shows two values, V1 and V2, in columns A and B. We will use the Google Sheets AND function to find the output using operators.
The procedure to find results using the operators are as follows:
Select cell C2 and enter the formula =AND(A2=B2), as shown below.
Press “Enter” to get the output shown below.
Important Things To Note
- The #VALUE! error occurs when an argument is blank or if no logical value is found.
- The AND function tests the conditions and returns TRUE or FALSE values.
Frequently Asked Questions
The AND function in Google Sheets is used when we have two or more conditions to test and wants the output value as TRUE or FALSE.
1. The function is used when several conditions need to be tested simultaneously.
2. It is used with the IF function when more than one condition must be tested.
3. The function is used with the OR function to compare two or more values.
We often forget in which category a function falls, here, the “AND” function. Then, we can insert the function as follows:
Choose an empty cell 🡢 select the “Insert” tab 🡢 click the “Function” option right arrow 🡢 click the “All” option right arrow 🡢 select the “AND” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the AND function by following the path shown below.
• Choose an empty cell 🡢 click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option 🡢 click the “All” option right arrow 🡢 select the “AND” function, as shown below.
Download Template
This article must help understand AND function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to AND Function In Google Sheets. We learn about the AND function, its formula and the operators used with examples & working template. You can learn more from the following articles. –
Leave a Reply