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 in Google Sheets evaluates one logical expression and returns a value depending on whether the expression is true or false. Interpretation of the Google Sheets IF formula: If the value in the D1 cell equals one ( Nested IF Google Sheets statements for multiple logical expressionsLet’s say you need to evaluate multiple logical expressions. For this, you can nest multiple IF statements Google Sheets 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)))) Example of a nested IF formula Google Sheets=IF(D1>0, SUM(B2:B), IF(D1=0, "Nothing", IF(D1<0, AVERAGE(B2:B)))) Interpretation of the nested IF Google Sheets formula: If the value in the D1 cell is above zero ( Nested IF statements can be improved with other logical functions: AND and OR. IF + AND/OR Google Sheets for multiple logical expressionsAND and OR Google Sheets functions 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 Google Sheets formula example=IF(AND(D1>0,D2>0,D3>0),SUM(B2:B),"Nothing") Interpretation of the IF AND Google Sheets formula: If the values in the cells D1 ( IF+OR Google Sheets formula example=IF(OR(D1>0,D2>0,D3>0),SUM(B2:B),"Nothing") Interpretation of the IF OR Google Sheets formula: If the value in the cell D1 ( IF+AND+OR formula Google Sheets example=IF(OR(AND(D1>0,D2>0),AND(E1<0,E2<0)),SUM(B2:B),"Nothing") Interpretation of the IF AND OR Google Sheets formula: If the values in cells D1 and D2 are above zero (
IFS Google Sheets function explainedIFS Google Sheets syntax=IFS(logical_expression#1, value_if_true, logical_expression#2, value_if_true, logical_expression#3, value_if_true,...) IFS Google Sheets function evaluates multiple logical expressions and returns the first true value. If all the logical expressions are false, the function returns #N/A. IFS Google Sheets formula example=IFS(D1>0,SUM(B2:B),D1=0,"Nothing",D1<0,AVERAGE(B2:B)) Interpretation of the IFS Google Sheets formula: If the value in the D1 cell is above zero ( IFS vs. multiple IF statements Google Sheets
The Google Sheets IFS function rests on true values only – it does not have IFS vs. nested IF statements exampleWe have three logic expressions:
And that’s how they work in Google Sheets: The Google Sheets 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. IF, AND, OR, IFS formula examples in real-life use casesNow, 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.
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.
Sales metrics calculated with IF and IFS formulasWe’ve built a sales monitor, which lets you track a few metrics filtered by year and country:
To demonstrate you the difference between the functions, we’ve calculated the metrics for the dashboard in two different ways:
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 metricNested 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 function ={"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 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 metricNested IF statements ={"Revenue"; IF( AND( ISBLANK(B3), ISBLANK(B5)), SUMIF('Pipedrive Deals'!AL2:AL,"won",'Pipedrive Deals'!AF2:AF), IF( ISBLANK(B5), SUM(IFERROR( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!Z2:Z=B3,'Pipedrive Deals'!AL2:AL="won"))), IF( ISBLANK(B3), SUM(IFERROR( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!CN2:CN=B5,'Pipedrive Deals'!AL2:AL="won"))), SUM(IFERROR( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!Z2:Z=B3,'Pipedrive Deals'!CN2:CN=B5,'Pipedrive Deals'!AL2:AL="won"))))))} IFS function ={"Revenue"; IFS( AND( ISBLANK(B3), ISBLANK(B5)), SUMIF('Pipedrive Deals'!AL2:AL,"won",'Pipedrive Deals'!AF2:AF), ISBLANK(B3), SUM( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!Z2:Z=B5,'Pipedrive Deals'!AL2:AL="won")), ISBLANK(B5), SUM(IFERROR( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!CN2:CN=B3,'Pipedrive Deals'!AL2:AL="won"))), AND( NOT(ISBLANK(B3)), NOT(ISBLANK(B5))), SUM(IFERROR( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!Z2:Z=B3,'Pipedrive Deals'!CN2:CN=B5,'Pipedrive Deals'!AL2:AL="won"))))} 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 metricNested IF statements ={"Value"; IF( AND( ISBLANK(B3), ISBLANK(B5)), SUM('Pipedrive Deals'!AF2:AF), IF( ISBLANK(B5), SUM(IFERROR( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!Z2:Z=B3))), IF( ISBLANK(B3), SUM(IFERROR( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!CN2:CN=B5))), SUM(IFERROR( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!Z2:Z=B3,'Pipedrive Deals'!CN2:CN=B5))))))} IFS function ={"Value"; IFS( AND( ISBLANK(B3), ISBLANK(B5)), SUMIF('Pipedrive Deals'!AF2:AF), ISBLANK(B5), SUM(IFERROR( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!Z2:Z=B3))), ISBLANK(B3), SUM(IFERROR( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!CN2:CN=B5))), AND( NOT( ISBLANK(B3)), NOT(ISBLANK(B5))), SUM( IFERROR( Filter('Pipedrive Deals'!AF2:AF,'Pipedrive Deals'!Z2:Z=B3,'Pipedrive Deals'!CN2:CN=B5))))} 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 or nested IF statements in Google Sheets – 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! Back to Blog Focus on your business goals while we take care of your data!Try Coupler.io Is there an AND operator in Google Sheets?The AND function returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.
What does mean in sheets?Google Sheets Comparison Operator “<>” and Function NE (Not Equal To) When you want to check whether the value in one cell is not equal to the value in another cell, you can use the “<>” comparison operator in Google Sheets or the similar function NE.
What does ampersand do in Google Sheets?An Ampersand operator is just a synonym for the CONCATENATE() function. When you specify an Ampersand operator in an expression, AppSheet converts that portion of the expression into a CONCATENATE() function. Using an Ampersand operator is simply a compact way of specifying the CONCATENATE() function.
How do I combine two formulas in Google Sheets?How do I combine two formulas in google sheets? To combine two formulas in one cell, use the concatenate function or the binary concatenate operator.
|