Google sheets if all cells are true

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 you prefer watching to reading, check out this simple tutorial by Railsware Product Academy on how to use the IF function (IFS, Nested IFs) in Google Sheets.

IF function explained 

IF Syntax

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

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).

How to Link Data Between Multiple Spreadsheets

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:

Nested IF formula example:

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). 

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

* 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

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). 

IF+OR formula example

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). 

IF+AND+OR formula example

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). 

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 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

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). 

How does an IFS formula differ from nested IF statements?

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”.

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:

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

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 a database from Pipedrive directly into spreadsheets first.

To do so, we’ll use Coupler.io – a tool that allows you to export data automatically and set a custom schedule for the updates. This means you can connect Pipedrive or another app to Google Sheets, and then the tool will regularly refresh your data in the spreadsheet. This is rather convenient because your calculations in Google Sheets will also be updated automatically, according to the latest numbers.

If you need to export your data to Google Sheets from other sources, you can use the same process we describe below. Just select your app as a data source instead of Pipedrive. All the other steps will be pretty similar.

• Sign up for Coupler.io. Select Pipedrive as a source and Google Sheets as a destination.

Other possible destinations are Microsoft Excel and BigQuery. The alternative sources can be HubSpot, Shopify, Quickbooks, and many other apps. See the full list of the supported integrations.

• Connect your Pipedrive account and select the data entity to import (Deals, Persons, Organizations, etc.). Then connect your Google account, choose the spreadsheet and the sheet for importing.

• Set your schedule for the automatic updates (this step is optional). Toggle on the Automatic data refresh and specify when you want your data to be refreshed.

• Press Save and Run. Your data has been transferred to Google Sheets. Now we can apply the IF, AND, OR and IFS functions to build the sales dashboard.

Load data to Google Sheets from your source with just a few clicks

Sales metrics calculated with IF and IFS formulas

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

• Total deals
  • Deals open
  • Deals closed
• Revenue
• Value of all deals open 
• Average days per deal

To demonstrate you the difference between the functions, we’ve calculated the metrics for the dashboard in two different ways:

• On the left, we’ve used nested IF formulas.
• On the right, we’ve used the IFS functions.

As you can see, both approaches work correctly and eventually give you the same numbers.

To build this dashboard, we’ve used the data we imported to Google Sheets one step earlier. In our case, it’s Pipedrive deals. We store this data on a separate sheet, and all the formulas in our dashboard are linked to this data. You can take a look at the imported Pipedrive deals to better understand the formulas we use for the dashboard.

Since we’re mostly interested in formulas right now, our dashboard only includes simple scorecards without complex graphs and charts. But if you want to see how to build visually rich dashboards with maps, pie charts, etc., you can check out our article on Google Sheets Sales Dashboard. 

Now, let’s zoom in a bit and see several formula examples we’ve used for the dashboard.

Formulas for the Total Deals metric

Nested IF statements

IFS functions

In fact, we didn’t encounter any significant practical differences between 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 lets you avoid nesting IF functions in a single formula, and that’s it.

Formulas for the Revenue metric

Nested IF statements

IFS functions

Here, again, we have a slight difference in the length, and a somewhat simpler logic for the IFS version. So, the conclusion is, if your criteria have parallel logic, the IFS function seems to be a better alternative.

Let’s see one last real-life example before we wrap up.

Formulas for the Value metric

Nested IF statements

IFS functions

In this example, the IF formula is noticeably shorter and seems to be a bit lighter and simpler. As you can see, everything depends on a specific case, so, eventually, you can just stick to whichever option you feel the most comfortable with and then adapt to your specific context on the go, if needed.

IFS formulas or nested IF statements – which are better?

Within this example, we can speculate 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 and Excel. Enjoy your data and good luck!