How to use the AND and OR functions in Google Sheets
If you ever want to check if your Google Sheets spreadsheet data meets certain criteria, you can use the AND and OR functions. These logical functions provide you with TRUE and FALSE responses, which you can use to organize your data.
If you use AND with multiple arguments, all of those arguments must be true for a TRUE response; otherwise, the AND function will return FALSE results. If you use OR, only one of the correct arguments, the OR function, will provide the TRUE response.
You can use AND and OR separately or together with other functions, such as IF.
Use the AND function in Google Sheets
You can use the AND function yourself or in combination with other functions to provide a logical test (TRUE or FALSE).
To get started, open Google Sheets and click a blank cell. Import:
=AND(Argument A, Argument B)
Replace each argument with the criteria you want to use. You can use as many arguments as you want, but you must have at least one argument for AND to work.
In the example below, the article used 3 arguments:
- The first argument is the simple calculation 1 + 1 = 2 .
- The second argument indicates that cell E3 is equal to 17.
- Finally, the third argument indicates that the value of cell F3 (is 3 ) is equal to the calculation 4-1.
Because all three arguments are correct, the AND formula returns TRUE in cell A2. If any of these arguments are changed, it will cause the AND formula in A2 to change the response from TRUE to FALSE.
The AND formula returns TRUE in cell A2In the example below, the AND formula in cell A3 has two correct arguments and one is incorrect ( F3 = 10 , while F3 is actually equal to 3 ). This causes the AND function to give FALSE feedback.
The AND function gives a FALSE responseUse the OR function in Google Sheets
While AND requires all the arguments it uses to be true, OR requires only one argument to be true to give TRUE feedback.
Like AND, you can use the OR function alone or combine it with other functions. As with AND, you can use as many arguments as you want, but you must have at least one argument for the function to work.
To use OR, click a blank cell and enter:
=OR(Argument A, Argument B)
Replace with your arguments.
In the example below, the formula using OR in cell A2 has an incorrect argument ( F3 = 10 , where F3 is actually equal to 3 ).
Unlike when you use AND, one of the three incorrect arguments still results in TRUE. For FALSE results, all arguments you use must be incorrect.
One of the three incorrect arguments still gives TRUE resultsIn the example below, the OR formula in cells A4 and A5 returns the FALSE response, because all three arguments in the two formulas are incorrect.
The OR formula in cells A4 and A5 returns the FALSE responseUse AND and OR with IF
Because AND and OR are logical functions with TRUE and FALSE responses, you can also use them with IF. When you use IF, if an argument is TRUE, it will return a value, otherwise the function will return another value.
The format of the formula using IF is:
=IF(Argument, Value IF TRUE, Value IF FALSE)
For example, as shown below, = IF (E2 = 1,3,4) causes IF to return 3 , if cell E2 equals 1 ; otherwise it returns 4 .
= IF (E2 = 1,3,4) causes IF to return 3, if cell E2 equals 1Because IF only supports a single argument, you can use AND and OR to perform complex logical tests with multiple arguments.
Use AND with IF
To use AND in an IF formula, enter:
=IF(AND(AND Argument 1), Value IF TRUE, Value IF FALSE)
In the example below, the article used IF with the same AND formula nested in cell A2 with 4 arguments. All 4 arguments are true, so the IF TRUE value (in this case, Yes ) is returned.
All 4 arguments are true, so the IF TRUE (Yes) value is returnedIn cell A3, an IF formula similar to AND contains two incorrect arguments. Because AND requires all arguments to be correct, IF returns the IF FALSE value , which is a different text value ( No ).
Use OR with IF
As with AND, you can also use OR with IF to create a complex logical test. Only one OR argument must be correct for IF to return a TRUE response.
To use OR with IF, click a blank cell and enter:
=IF(OR(OR Argument 1), Value IF TRUE, Value IF FALSE)
Replace with the OR argument (s) and your IF TRUE / FALSE value.
In the examples below, the two IF formulas with OR in cells A2 and A3 return the text value IF TRUE ( Yes ). All 4 arguments are true in the A2 IF formula with OR, while A3 has 2 of 4 incorrect arguments.
Two IF formulas with OR in cells A2 and A3 return the text value IF TRUE (Yes)In cell A4, all 4 arguments in an IF formula with OR are incorrect. This is why the IF formula returns the IF FALSE ( No ) text value instead.
You should read it
- How to use the SMALL function in Google Sheets
- 30+ useful Google Sheets functions
- 9 basic Google Sheets functions you should know
- How to use the UNIQUE function in Google Sheets
- How to use the MEDIAN function in Google Sheets
- How to use the FLOOR function in Google Sheets
- How to count words on Google Sheets
- How to generate random numbers in Google Sheets
- Keep track of the stock market with Google Sheets
- How to use the QUERY function in Google Sheets
- Tricks using Google Sheets should not be ignored
- How to use Filter function on Google Sheets
Maybe you are interested
How to check computer IP with TrueIP
Push - Application that uses the iPhone's TrueDepth camera to count the number of push-ups
Truecaller allows the use of voice-cloning AI to answer calls
Top 10 best cheap True Wireless headphones of 2023
Microsoft says there is an option to install Windows 11 taskbar that reduces laptop battery life, is it true?
10 Tried-and-True Methods for Quickly Increasing Your Instagram Likes