# IF, IFS, AND, OR: Google Sheets Logical Expressions with Real-Life Examples

**IF** is a Google Sheets function that acts based on a given condition. You provide a boolean and tell what to do based on whether it’s TRUE or FALSE. You can combine IF with other logical functions – **AND**, **OR **– to create nested formulas and go over multiple sets of criteria. But should you? **IFS** is a dedicated function, which evaluates multiple conditions to return a value. However, sometimes **nested IF statements** do better than IFS. Let’s explore some real-life examples and find out which logical function is a go.

## IF function explained

### IF Syntax

=IF(logical_expression, value_if_true, value_if_false)

IF function evaluates **one logical expression** and returns a value depending on whether the expression is true or false. `value_if_false`

is blank by default.

### IF formula example

=IF(D1=1,SUM(B2:B),"")

Interpretation:

If the value in the D1 cell equals one (`logical_expression`

), then the formula will count the sum of values in the range B2:B (`value_if_true`

). Otherwise, the formula will return an empty cell (`value_if_false`

).

You can check out this formula example in the spreadsheet.

## Nested IF Google Sheets statements for multiple logical expressions

Let’s say you need to evaluate **multiple logical expressions**. For this, you can nest multiple IF statements in a single formula. It may look as follows:

=IF(logical_expression#1, value_if_true, IF(logical_expression#2, value_if_true, IF(logical_expression#3, value_if_true, IF(logical_expression#4,value_if_true,value_if_false))))

### Nested IF formula example:

=IF(D1>0, SUM(B2:B), IF(D1=0, "Nothing", IF(D1<0, AVERAGE(B2:B))))

Interpretation:

If the value in the D1 cell is above zero (`logical_expression#1`

), then the formula will return the sum of values in the range B2:B (`value_if_true`

); if the D1 cell is empty or its value is zero (`logical_expression#2`

), then the formula will return “*Nothing*” (`value_if_true`

); if the value in the D1 cell is below zero (`logical_expression#3`

), then the formula will return the average of values in the range B2:B (`value_if_true`

).

You can check out this formula example in the spreadsheet.

Nested IF statements can be improved with other logical functions: **AND** and **OR**.

## IF + AND/OR for multiple logical expressions

### AND function and OR function explained

AND function | OR function | |

Returns TRUE | If all of the provided logical expressions are logically true* | If any of the provided logical expressions is logically true |

Returns FALSE | If any of the provided logical expressions is logically false | If all of the provided logical expressions are logically false |

Syntax | `=AND(logical_expression1, [logical_expression2, ...])` | `=OR(logical_expression1, [logical_expression2, ...])` |

** All numbers (including negative ones) are logically true. The number 0 is logically false.*

Let’s combine AND/OR with IF and check out how this works:

### IF+AND formula example

=IF(AND(D1>0,D2>0,D3>0),SUM(B2:B),"Nothing")

Interpretation:

If the values in the cells D1 (`logical_expression#1`

), D2 (`logical_expression#2`

), and D3 (`logical_expression#3`

) are above zero, then the formula will return the sum of values in the range B2:B (`value_if_true`

). Otherwise, if **any of the logical expressions is false**, the formula will return “*Nothing*” (`value_if_false`

).

You can check out this formula example in the spreadsheet.

### IF+OR formula example

=IF(OR(D1>0,D2>0,D3>0),SUM(B2:B),"Nothing")

Interpretation:

If the value in the cell D1 (`logical_expression#1`

), or D2 (`logical_expression#2`

), or D3 (`logical_expression#3`

) is above zero, then the formula will return the sum of values in the range B2:B (`value_if_true`

). Otherwise, if **all of the logical expressions are false**, the formula will return “*Nothing*” (`value_if_false`

).

You can check out this formula example in the spreadsheet.

### IF+AND+OR formula example

=IF(OR(AND(D1>0,D2>0),AND(E1<0,E2<0)),SUM(B2:B),"Nothing")

Interpretation:

If the values in cells D1 and D2 are above zero (`logical_expression#1`

), or the values in cells E1 and E2 are below zero (`logical_expression#2`

), then the formula will return the sum of values in the range B2:B (`value_if_true`

). Otherwise, if **all of the logical expressions are false**, the formula will return “*Nothing*” (`value_if_false`

).

You can check out this formula example in the spreadsheet.

Logical operators (AND and OR) let you include versatile conditions in your IF formula.

Keep in mind that AND/OR don’t work in arrays. Besides, multiple IF statements can be quite difficult to build and maintain. An alternative solution is to go with the IFS function.

## IFS function explained

### IFS Syntax

=IFS(logical_expression#1, value_if_true, logical_expression#2, value_if_true, logical_expression#3, value_if_true,...)

IFS function evaluates **multiple logical expressions** and returns the first true value. If all the logical expressions are false, the function returns *#N/A*.

### IFS formula example

=IFS(D1>0,SUM(B2:B),D1=0,"Nothing",D1<0,AVERAGE(B2:B))

Interpretation:

If the value in the D1 cell is above zero (`logical_expression#1`

), then the formula will return the sum of values in the range B2:B (`value_if_true`

); if the D1 cell is empty or its value equals zero (`logical_expression#2`

), then the formula will return “*Nothing*” (`value_if_true`

); if the value in the D1 cell is below zero (`logical_expression#3`

), then the formula will return the average of values in the range B2:B (`value_if_true`

).

You can check out this formula example in the spreadsheet.

## How does an IFS formula differ from nested IF statements?

Nested IF formula | IFS formula |

`=IF(logical_expression#1, value_if_true,` `IF(logical_expression#2, value_if_true,` `IF(logical_expression#3, value_if_true,value_if_false)))` | `=IFS(logical_expression#1, value_if_true,` `logical_expression#2, value_if_true,` `logical_expression#3, value_if_true)` |

The IFS function rests on true values only – it does not have `value_if_false`

. But the major difference between IF and IFS can be revealed when dealing with arrays. Let’s explore this through an example.

### IFS vs. nested IF statements example

We have three logic expressions:

- if the value in the A1 cell equals 1, then show “A” and “B”
- if the value in the A1 cell equals 2, then show “C” and “D”
- if the A1 cell is empty, then show “E” and “F”.

Nested IF statements formula | IFS formula |

`=IF(A1=1,{"A";"B"},IF(A1=2,{"C";"D"},{"E";"F"}))` | `=IFS(A1=1,{"A";"B"},A1=2,{"C";"D"},A1="",{"E";"F"})` |

And that’s how they work in Google Sheets:

The IFS function returns a single-cell output. To return an arrayed output, IFS expects an arrayed input, such as:

=ARRAYFORMULA( IFS(A1:A2=1, {"A";"B"}, A1:A2=2, {"C";"D"}, A1:A2="", {"E";"F"} )

In this case, however, the input range includes two cells: A1 and A2.

You can check out this formula example in the spreadsheet.

## IF, AND, OR, IFS formula examples in real-life use cases

Now, let’s check some real-life examples for you to understand the power of logical functions available in Google Sheets. We’re going to build a sales tracker using IF, AND, OR and IFS functions. The sales CRM software we use to store data is Pipedrive. So, let’s export the database from Pipedrive directly into spreadsheets first.

### Importing data into Google Sheets

Earlier, we blogged about how to integrate Pipedrive and Google Sheets. Here are the steps:

**Install****Coupler.io**– a GSheets add-on for importing data from various apps**Set up a Pipedrive importer**– specify what you’re going to import (Deals, Persons, or Organizations) and connect your Pipedrive account to Coupler.io. Also you can enable**automatic data refresh**at a chosen schedule:

**Run the importer**to get your Pipedrive data in a spreadsheet.

### Building a sales tracker

We’ve built a sales monitor, which lets you track a few metrics filtered by year and country:

- Total deals
- Deals open
- Deals closed
- Deals won
- Deals lost

- Revenue
- Value of all deals open
- Average days per deal

You can download thesales tracker templateand adjust it for your needs.

In this blog post, we’re not going to explain the details of building the sales monitor, but if you need those, check out the Google Sheets Sales Dashboard.

### Nested IF statements or IFS – which is better?

Our sales tracker consists of two parts. The first part is made using IF statements, and the second one is built entirely with IFS. For example, here’s how the formula for *Total deals* looks in both versions:

**Nested IF statements**

={"Total deals"; IF( AND( ISBLANK(B3), ISBLANK(B5)), COUNTA('Pipedrive Deals'!A2:A), IF( ISBLANK(B5), COUNTA( Filter('Pipedrive Deals'!A2:A,'Pipedrive Deals'!Z2:Z=B3)), IF( ISBLANK(B3), COUNTA( FILTER('Pipedrive Deals'!A2:A,'Pipedrive Deals'!CN2:CN=B5)), COUNTA( FILTER('Pipedrive Deals'!A2:A,'Pipedrive Deals'!Z2:Z=B3,'Pipedrive Deals'!CN2:CN=B5)))))}

**IFS**

={"Total deals"; IFS( AND( ISBLANK(B3), ISBLANK(B5)), COUNTA('Pipedrive Deals'!A2:A), ISBLANK(B5), COUNTA( Filter('Pipedrive Deals'!A2:A,'Pipedrive Deals'!Z2:Z=B3)), ISBLANK(B3), COUNTA( FILTER('Pipedrive Deals'!A2:A,'Pipedrive Deals'!CN2:CN=B5)), AND( NOT( ISBLANK(B3)), NOT( ISBLANK(B5))), COUNTA( FILTER('Pipedrive Deals'!A2:A,'Pipedrive Deals'!Z2:Z=B3,'Pipedrive Deals'!CN2:CN=B5)))}

In practice, we did not face significant differences in the behavior of the two approaches. The IFS formula is longer (*360 characters excluding spaces vs. 328 for the formula with nested IF statements*). From the standpoint of maintainability, there is no difference either. IFS let’s you avoid nesting IF functions in a single formula, and that’s it.

So, if your criteria have parallel logic, the IFS function seems to be a better alternative.

## To wrap up

Within this example, we can suppose that IFS is just a shorthand for nested IF statements. But that’s not the fundamental truth, since each use case has its own requirements. Anyway, now you know what you can do with IF and IFS, as well as logical operators AND and OR. And don’t forget about Coupler.io, which can simplify and automate data import to Google Sheets. Enjoy your data and good luck!

Back to Blog