Using logical functions together in Google Sheets

Using logical functions together in Google Sheets
Reading Time: 4 minutes
Logical Operators featured image
Logical functions together in Google Sheets

We will have a look at individual logical functions together in Google Sheets ( AND, OR, IF ) and see how to use them together along with some examples.

Why use logical functions together in Google Sheets

Logical functions are used in spreadsheets to test whether a situation is true or false.

Google Sheets gives you access to very powerful logical Functions such as IF, AND and OR functions. These should cover most of the cases but there might be more advanced case scenarios where using these alone won’t suffice. In that case, we will have to be little creative and use the logical functions together in Google Sheets.

We will cover these cases in detail later and see how to come up with logic to use logical functions together in Google Sheets.

IF logical operator

If the logical operator is a conditional operator that returns one value if a certain condition is met, otherwise it returns another value. The syntax for the same is 

=IF(condition, value_if_true, value_if_false)

Sample Usage

=IF(B2>40, “Pass”, “Fail”)

If logical function
Figure 1: IF logical function

Here in this example, we are checking if the student has scored more than 40 marks. If yes, we declare him passed otherwise failed.

AND logical operator

AND operator is an operator which returns true only if all the arguments are true. Otherwise, it returns false. The syntax for the same is as follows:

=AND(logical_expression1, [logical_expression2, …])

There can be multiple arguments for this function.

Sample usage

=AND(A1<100, A2<100)

AND logical function
Figure 2: AND logical function

Here B4 was true only because both the values A1 and A2 are less than 100.

OR logical function

OR is a logical operator that returns TRUE if any one of its arguments is true, else, it returns false. The syntax for the same is as follows:

=OR(logical_expression1, [logical_expression2, …])

Just like AND logical function, there can be multiple arguments for this function.

OR logical function
Figure 3: OR logical function

Here were can see since A1 is less than 100, the result is TRUE.

Combined use of IF, AND, OR logical functions

Let’s have a look at two situations.

The first situation is a report card. If the student has obtained a mark higher than 40 in all the subjects, he is declared Passed, otherwise Failed.

To achieve the given task we have used the IF function along with AND.

Formula used:

=IF(AND(B2>40, C2>40, D2>40), “Pass”, “Fail”)

The AND function returns true or false and based on that the IF function displays the text. 

Using IF and AND logical functions together in Google Sheets
Figure 4: Using IF and AND logical functions together in Google Sheets

In the next example, let’s have a look at a sports match. If the player wins any two rounds, then he or she is declared the winner. This problem can be solved by using the given formula.

Formula used:

=IF(OR(AND(B2=1, C2=1), AND(C2=1, D2=1), AND(B2=1, D2=1)), “Win”, “Lose”)

We are using the combination of IF, OR & AND. There can be three combinations possible to win for the AND condition. If any of those is true, then the player would win the match. Hence, the OR condition. Lastly, we have used the IF condition to conditionally display the text.

IF, AND, and OR together
Figure 5: Using IF, AND, OR logical functions together in Google Sheets

Conclusion

Now you know how to use OR, AND, IF logical functions together in Google Sheets along with the syntax for each and every individual function. We also saw some interesting examples where they can be used.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Validate URLs in Google Sheets: Learn how to Validate URLs using Conditional Formatting in Google Sheets

Google Sheets Left and Right Functions: Learn how to use the LEFT and RIGHT Functions in Google Sheets.

2 responses to “Using logical functions together in Google Sheets”

Leave a Reply