AND(), OR(), and NOT(): step up your conditions with some boolean logic

Overview

As you become more adept at spreadsheets or start working with more advanced data, you’ll find yourself needing more and more complex formulas and conditions. Of course, you probably know how to use an IF() function and nest it - but we’d like to help you get started with the lesser-known, but extremely useful, AND(), OR(), and NOT() functions :smile:. Before we do any of that, let’s explain a little about what boolean logic actually is.

Boolean logic

What is boolean logic? Basically, it’s any question (known as a condition) that when answered, can be either TRUE or FALSE.

For example:

  • Is the sun yellow? Yes (True)
  • Is it raining today? No (False).

But why do you need to know how to do this in a spreadsheet? Well, anytime you use an IF() statement, you’re actually using some boolean logic:

=IF(4>3, "That's TRUE!", "This is FALSE :(")

The first part of any IF() function is actually checking whether or not a condition is TRUE. In the example above, we’re checking whether or not three is bigger than four (3>4). This is TRUE, so we return "That's TRUE!"

But, if we change it:

=IF(3>4, "That's TRUE!", "This is FALSE.")

Then the condition, 3>4 is no longer TRUE, which means that the function will return "This is FALSE :(".

Now, IF() is pretty easy - but what if we want to check more than one condition? Well, that’s when our lovely AND(), OR(), and NOT() functions come into play.

AND

We use AND() when we want to evaluate more than one condition at the same time, and they all have to be TRUE. If any of the conditions turn out to be FALSE, AND() returns FALSE. Have a look at the diagram below:

boolean-and-example

As you can see, unless all three conditions equal TRUE, AND() returns FALSE. This is really handy when you’re trying to, for example, find a company that is both from Berlin and has more than USD 2 million in funding, and has more than 20 employees.

Without the AND() function, you’d need to start thinking about some nested IF() functions that in reality, would just make your formulas complicated and cumbersome.

Syntax

AND(condition1, [condition2], [...])

Parameter Mandatory Description
condition1 Yes Some statement that evaluates to either TRUE or FALSE.
condition2 No Some other statement that evaluates to either TRUE or FALSE.

Even though condition2 is not mandatory, it doesn’t make much sense to not have one, as the whole point of AND() is to evaluate more than one condition :stuck_out_tongue:.

Examples

Now, in what situation would you actually use AND(), and how can you do it practically?

Quickly set up a view with the following content, and if you don’t already have it, connect your dashdash account with our Crunchbase integration:

Cell Content
A1 Company
A2 dashdash
B1 Location
B2 Berlin
C1 Employees
C2 20
D1 Info

Now, say we have a situation where you want to get company info for companies that are in Berlin and have 20 or more employees. This is exactly when you would use the AND function!

=IF(AND(B2="Berlin",C2>=20),GET_COMPANY_DETAIL_CRUNCHBASE(A2),"Not a supported location or not enough employees")

In “human” terms, this is how you can think about it:

If the company’s location is Berlin and they have 20 or more employees, get details about them from Crunchbase. If not, then display Not a supported location or not enough employees.

AND example

This can get a lot more advanced - but the logic is always the same. Everything has to answer to TRUE, and then it returns true.

But what if you just want either condition to be TRUE- not both of them? Well, that’s when we use OR!

OR

OR() is brilliant for when you need to check more than one condition, but you only need one of the conditions to be TRUE to do anything. Remember, with AND() all the conditions need to be TRUE in order for the function to return TRUE. With OR() - you just need one of them!
Check out the screenshot below:

boolean-or-example

As you can see, unless all the conditions are FALSE, OR() returns TRUE. Now, this is particularly handy in the case that you just need one of the conditions to be TRUE.

For example, say you’re looking for companies that are either located in Berlin or in Porto, then you’d use OR()!

Syntax

OR(condition1, [condition2], [...])

Parameter Mandatory Description
condition1 Yes Some statement that evaluates to either TRUE or FALSE.
condition2 No Some other statement that evaluates to either TRUE or FALSE.

Examples

Let’s go back to the view we created for AND(). This time, we’re going to assume that we want to get information for companies that are in Berlin or have at least 20 employees:

=IF(OR(B2="Berlin",C2>=20),GET_COMPANY_DETAIL_CRUNCHBASE(A2),"Not a supported location or not enough employees")

Now, you can also nest the OR() within an AND(), or vice versa. Going back to our previous example, let’s say we want to search for companies that are either in Berlin or Porto and have at least 20 employees.

=IF(AND(OR(B2="Berlin",B2="Porto"),C2>=20),GET_COMPANY_DETAIL_CRUNCHBASE(A2),"Not a supported location or not enough employees")

It might be a bit confusing, so just to break this example down:

  1. AND(condition1, condition2) - here, we know that we want both our conditions to be TRUE. The company must be located either in Porto or in Berlin and it must have at least 20 employees.
  2. AND(OR(condition1, condition2), C2>=20) - now we added the OR()function, in order to evaluate if the company is either in Berlin or in Porto.
  3. AND(OR(B2="Berlin", B2="Porto"), C2>=20) - and that’s that!
Nested AND and OR example

And now, just to make it trickier - what if you wanted to get details for a company from anywhere else BUT Berlin and Porto? Instead of rewriting ourIF() function, we can just use NOT() :sunglasses:.

NOT

NOT() is very simple… it’ll always return the opposite condition. So, NOT(FALSE) returns TRUE, while NOT(TRUE) returns FALSE. It’s that simple! But still, you can have a look at the screenshot below just to make sure:
boolean-not-example

Syntax

NOT(argument)

Parameter Mandatory Description
argument Yes Some conditional statement that evaluates to either TRUE or FALSE.

Examples

Why might you want to use it?

Let’s go one more time to our example. Let’s say that we want to get a company’s details only if the location is not Berlin or Porto. In that case:

=IF(AND(NOT(OR(B2="Berlin",B2="Porto")),C2>=20),GET_COMPANY_DETAIL_CRUNCHBASE(A2),"Not a supported location or not enough employees")

All we’re doing is wrapping our OR() function inside the NOT() function, which will in turn always return the opposite of what OR() returns.

Nested NOT example

Challenge

Right, so let’s try out some logical challenges using AND(), OR(), and NOT(). We’ll still be using the same Crunchbase function as before, but you might need to add some additional columns here or there to add some data.

Challenge 1

How would you get information for companies that are only located in Berlin, have more than 50 employees, and are in the Software industry?

=IF(AND(B2="Berlin",C2>=20,D2="Software"),GET_COMPANY_DETAIL_CRUNCHBASE(A2),"")

Challenge 2

How would you get information for companies that are located either in Lisbon or Hamburg, and have at least 20 employees, and are either in the Software or IT industry?

=IF(AND(OR(B2="Lisbon",B2="Hamburg"),C2>=20,OR(D2="Software",D2="IT")),GET_COMPANY_DETAIL_CRUNCHBASE(A2),"")

Challenge 3

How would you get information for companies that are located either in Lisbon or Hamburg, and have at least 20 employees but no more than 30, and are either in the Software or IT industry?

=IF(AND(OR(B2="Lisbon",B2="Hamburg"),C2>=20,C2<=30,OR(D2="Software",D2="IT")),GET_COMPANY_DETAIL_CRUNCHBASE(A2),"")

Challenge 4

How would you get information for companies that are not located either in Lisbon or Hamburg, and have either less than 20 employees or more than 30 employees and are either in the Software or IT industry?

=IF(AND(NOT(OR(B2="Lisbon",B2="Hamburg")),NOT(AND(C2>=20,C2<=30)),OR(D2="Software",D2="IT")),GET_COMPANY_DETAIL_CRUNCHBASE(A2),"")

Extra challenge 5

For this one, using your knowledge of JsonPath and PARSE(), use the formula from Challenge 4, but only return the domain for the first company that matches your criteria :wink:

=IF(AND(NOT(OR(B2="Lisbon",B2="Hamburg")),NOT(AND(C2>=20,C2<=30)),OR(D2="Software",D2="IT")),PARSE(GET_COMPANY_DETAIL_CRUNCHBASE(A2),"['organizations'][0].['domain']"),"")

Summary

Nice job! If managed to get through those challenges, you’ve definitely got some handy skills that you can put to work in your spreadsheets AND make them even better. OR, perhaps you want to play around and make things a little bit more complicated. And don’t worry - we’ll be putting some all this to practice in our tutorial series next week - so be sure NOT to miss that. Until then, happy building :rocket: !

1 Like