Back to Blog

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 functionOR function
Returns TRUEIf all of the provided logical expressions are logically true*If any of the provided logical expressions is logically true
Returns FALSEIf any of the provided logical expressions is logically falseIf 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 formulaIFS 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 formulaIFS 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 the sales tracker template and 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

Access your data
in a simple format for free!

Start Free