Right Way To Use Google Sheets IF Function (6 Helpful Tips)

Written & Technically reviewed by Channa S Bandara.
Publish Date : May 7, 2021 | Review Date : May 9, 2021

Google Sheet, Spread Sheet

In google sheets have a powerful and most useful google function and formula. google sheets IF function is one of the powerful and most useful functions, among others.

What is IF function in google sheets?

Google sheets If function is a logical expression, it uses it to evaluate whether one is correct or not using pre-defined data in the cell. This expression returns a Boolean value, TRUE describe when if statement is correct otherwise it will confirm as FALSE.

This article describes what is IF function and how usage in a google spreadsheet.

google sheets IF function offers many more features and operations rather than logical statement handling let’s check how to use it.

IF() function obtain the following syntax

Logical expression: this contains the main criteria that express how the operation needs to run. You can use the following logical expression symbol to denote > “greater than”, >= “greater than or equal “, < ” less than “, <= “less than or equal “, <> “not equal”, and = ” equal “ respectively.
Assign a value instead of return: as mention before in the article google sheets offer to replace value instead of Boolean return (TRUE / FALSE). In statement always provide the function in the correct order, TRUE always 1st and FALSE 2nd in return argument. You can replace text with an inside double quote or number as well. This will help to emit meaning full result into cell and stakeholder will get clearly understand about the statement output.

How to use Google Sheets IF function?

About example

Take a look at the following exam data which held to offer entrance in medical faculty in university. in sheet contact student name, gender, number of attempts, exam paper mark, and practical mark. applicant can only take 3 attempts and the exam mark is define out of 100 . all below IF() statement example is base on this sample datasheet.

Download Example Sheet

#1. Regular IF() function in google sheets

this is a basic way to how to evaluate logical conditions with giving value in the cell it will return TRUE or FALSE.

To use google sheets IF Function, select the cell which you want to execute the query, then double click and ENTER =IF(statement, what if true, what if false).

Take a look at the following Example ?
Using the above example, our stakeholder needs to identify who os pass or Failed. that status needs to be updated on a separate row and if pass, initialize value as "Exam PASS" otherwise "EXAM FAILED"

Let check out how to solve this

Open a spreadsheet in Google Sheets.
The select cell you’d like to add an if statement (I decide to update on the F column) and double click on the related cell.
Then type =IF then you can see that list of formulas will drop on. just select =IF and press ENTER button. Google sheets smart & it will show all IF() the condition reference as the tooltip.
your exam mark represents in the D column our stakeholders need to get who PASS or FAILED that’s mean. if the exam marks greater than 75, the student will pass. if the exam marks less than 75 the student will be failed. So, in the above student data 1st row contains the header of each column and all data will start from the 2nd row in the spreadsheet. then I get D2 as my 1st value and defined it like this
=IF(D2 > 75,”EXAM PASS”,”EXAM FAILED”)
Now you can see that google sheet if the condition returns. data without any hassle.
To apply with all cells just click F2 cell and move the mouse drag square icon (right bottom in the cell ) then drag the mouse until the last data in the cell which defined value.

Please note that if you decide to add a text condition instead of a number, that express value need to define inside a double quote.

=IF(B2 > “MALE”,"He","She")

#2. Calculations with the IF Function in google sheets

This valuable feature that google sheets offer to execute mathematical calculation inside if statement.

To use google sheets IF Function with calculation, select the cell which you want to execute the query, then double click and ENTER =IF(mathematical calculation with the statement, what if true, what if false).

Take a look at the following Example ?
Using our example sheet, our stakeholder decides to change the mark calculation process. they decide to calculate marks using Exam Mark & Practical Mark. get the total mark of both and divide by 2 and get average. if average greater than 75 return "Exam PASS" otherwise "EXAM FAILED"

In this datasheet contain exam mark on D column and particle marks in E column.we decide to combine that 2 cell in if condition instead of move another column ( for total value ) . IF statement like below.

				
					=IF((D2+E2)/2 > 75,"EXAM PASS","EXAM FAILED")

Like the above condition, we calculate the total mark inside the bracket then divide it by 2 to return the exam mark.

To apply with all cells just click F2 cell and move the mouse drag square icon (right bottom in the cell ) then drag the mouse until the last data row which defined value.

#3. Multiple IF statements google sheets ( google sheets Nested IF )

This is simply called a nested function because it’s defined inside another condition. Basically, using nest IF, you can be repeating the statement multiple times. You can use many IF conditions in a statement it will act like IF ELSE condition.

Take a look at the following Example ?

Using our example sheet, our stakeholder decide to an only select past student who tried 3 attempts and the condition should be like this.

The total mark needs to calculate base on Exam Mark & Practical Mark column and divide by 2.
If exam pass shows the return value as "Exam PASS"
If the first attempt failed, show the return value as EXAM FAILED. 2 attempts remain.
If the second attempt failed, show the return value as EXAM FAILED last attempt remains.
If the third attempt failed, show the return value as EXAM FAILED. Cannot sit for this exam again.

This is a bit of complicated logic, so I create a simple flow chart to make it much easier to understand and implement,
You can see that if the statement is TURE just return the passed value in the main condition. But in the FALSE statement again evaluate student exam attempt exceeds the maximum. This operation will execute each statement to determine the result.

let‘s check how to implement it in google sheets

To use multiple conditions within the IF Function, double click the cell which you need to define IF() condition and type =IF(1st_argumant) then if true/false enter the 2nd statement =IF(2st_argumant). and you can continually use this condition as a nested operation.

				
					=IF((D2+E2)/2 > 75,"EXAM PASS",
      IF(C2 = 1,"EXAM FAILED. 2 attempts remain",
         IF(C2 = 2,"EXAM FAILED. 1 attempts remain",
            IF(C2 = 3,"EXAM FAILED.  Cannot sit for this exam again", "Please check Exam mark or Exam Attempt data your Enterd Correctly ")
         )
     )
   )
/* comment */
// line 2 = check if 1st attempt
// line 3 = check if 2nd attempt
// line 4 = check if 3rd attempt

#4. Using AND/OR/NOT Operators in google sheets IF Function

Reguler IF condition in google sheets dosent have way that operate multiple contion in single statement instead of that you can implement a nested IF statement. But your logic going to be complicated and it will be impacted spreadsheet performance too.

When you need to deal with multiple validations in single statement you have to implement a logical condition operator.

AND Operator :

Returns TRUE if all of its arguments are TRUE in Statement. If FALSE any of the provided arguments, Return Statement as FALSE.

You can implement value (text, number, boolean), cell, or Named range with AND operation. any logical expressions (>, >=, <, <=, <>, =) are allow to implement with this operation.

				
					AND(A2 = "foo", A3 = "bar") //  String validate contion 
AND(A2 = 10, A3 = 20) //  Numeric validate contion
AND(TRUE,FALSE,TRUE) // Booloean validate contion
AND(A1:A10,B1:B10)  // validate with range

To use AND operator within IF FUNCTION, a select cell which you want to execute the query, then double click and ENTER =IF(AND( statement), what if true, what if false), you can use any nested AND same like nested IF condition.

let‘s check how to implement AND OPERATION in google sheets

				
					=IF( AND((D2+E2)/2 > 75,C2 >= 1),
    "EXAM PASS",
        IF( AND(C2 >= 1,C2 < 3), 
            "EXAM FAILED. "&(3-C2)& " attempts remain", 
            "Cannot sit for this exam again"
         )
    )

OR Operator:

OR operation executes the opposite of AND Operation. It will return a TRUE value if any of the given arguments are logically true. You can reduce nested IF logic with OR operation.

you can implement value (text, number, Boolean), cell, or named range with OR operation. Any logical expressions (>, >=, <, <=, <>, =) are allowed to implement with this operation

				
					
OR(A2 = "foo", A3 = "bar") //  String validate condition 
OR(A2 = 10, A3 = 20) //  Numeric validate condition
OR(TRUE,FALSE,TRUE) // Booloean validate condition
OR(A1:A10,B1:B10)  // validate with range condition

To use OR operator within IF FUNCTION, select cell which you want to execute the query, then double click and ENTER =IF(OR( statement), what if true, what if false), you can use any nested OR same like nested IF condition.

let‘s check how to implement OR OPERATION in google sheets

				
					=IF(
     AND((D2+E2)/2 > 75,C2 >= 1),
     "EXAM PASS",
      IF(
        OR(C2 = 1,C2 = 2), 
           "EXAM FAILED. "&(3-C2)& " attempts remain", 
           "Cannot sit for this exam again"
      )
   )

NOT Operator:

this not much popular logical condition. but it can be used to validate data without using logical expression (<>). it will Return the opposite of value defined on the logical statement. That means if check NOT(TRUE), return FALSE otherwise if NOT(FALSE), Return TRUE. you can only implement in one value in NOT operation.

you can implement number, text, boolean data with NOT operation.

				
					NOT(A2 = "foo") //  String validate condition 
NOT(A2 = 1) //  Number validate condition 
NOT(A2 = B1) //  Cell validate condition

To use NOT operator within IF FUNCTION, a select cell which you want to execute the query, then double click and ENTER =IF(NOT(the cell reference you need to validate), what if true, what if false), you can use any nested OR same like nested IF condition.

Let‘s check how to implement NOT OPERATION in google sheets

				
					=IF( AND((D2+E2)/2 > 75,C2 >= 1), "EXAM PASS",
  IF(NOT(C2 = 3), 
      "EXAM FAILED. "&(3-C2)& " attempts remain",
      "Cannot sit for this exam again"
 )
)

#5. How to validate cell is blank or not blank in IF Function (ISBLANK)?

In IF() Logical statement executes within a given value in the sheet. If the cell is empty, the statement getting as the user giving a null value as cell referenced. If will impact total if condition and wrong return will emit.

Google sheet introduces a formula call ISBLANK to validate whether the referenced cell is empty.

				
					ISBLANK(A2)
IF(ISBLANK(A2),"Requried Fields are empty", "What if false")

ISBLANK can’t operate with multiple cell values. You need to use AND / OR operators if it executes with multiple sets of values in google sheets.

				
					
IF(OR(ISBLANK(A2), ISBLANK(B2)),"Requried Fields are empty", "what is false")

IF(AND(ISBLANK(A2), ISBLANK(B2)),"Requried Fields are empty", "what is false")

To use the ISBLANK operator within IF FUNCTION, a select cell which you want to execute the query, then double click and ENTER =IF(ISBLANK(the cell reference you need to check blank), what if true, what if false).

Let‘s check how to implement ISBLANK Formula in google sheets

				
					=IF(OR(ISBLANK(D2),ISBLANK(E2)),"Requried Fields are empty",
    IF(AND((D2+E2)/2 > 75,C2 >= 1),"EXAM PASS",
      IF(NOT(C2 = 3), 
         "EXAM FAILED. "&(3-C2)& " attempts remain",
         "Cannot sit for this exam again"
      )
   )
)

#6. How to Error handling using IFERROR in IF Statement?

When you deal with a datasheet sometimes accidentally will enter incorrect data it will getting errors when that data part of working with formulas in Google Sheets. In IF CONDITION always deal with the TRUE or FALSE return value. Your argument return will completely change if exit an incorrect value in the cell.

Google sheets introduce a formula call IFERROR to handle this kind of situation. You can implement if statement inside the IFERROR formula and your If statement will execute if it is don’t contain an error value, otherwise, it returns blank or your return value in IFERROR.

				
					IFERROR(A2) // check Is Error Exit
IFERROR(A1,"Error in cell A1") // return value if Error Exit

IFERROR can’t operate with multiple cell values. You need to use AND / OR operators if it executes with multiple sets of values in google sheets.

				
					=IFERROR(IF(OR((E2/2) > 10, (C2/2) > 10), "what if true", "what if false"),"Invalid value in given cell ref")

=IFERROR(IF(AND((E2/2) > 10, (C2/2) > 10), "what if true", "what if false"),"Invalid value in given cell ref")

To use IFERROR operator with IF FUNCTION, a select cell which you want to execute the query, then double click and ENTER =IFERROR(IF(a statement you need to validate, “what if true”, “what if false”), “what return if error existing”).

Let‘s check how to implement IFERROR Formula in google sheets

				
					=IFERROR(IF(OR(ISBLANK(D2),ISBLANK(E2)),"Requried Fields are empty",
  IF(AND((D2+E2)/2 > 75,C2 >= 1),"EXAM PASS",
         IF(NOT(C2 = 3), 
                "EXAM FAILED. "&(3-C2)& " attempts remain",
        "Cannot sit for this exam again"
       )
    )
),"Invalid value contain in given cell ref")

Frequency ask Question

Question: Is that possible to use multiple arguments in a single IF Statement?

Answer: you cannot execute with a regular IF Function. You have to use AND / OR logical formula together with IF Function in google sheets

Question: Can I create IF FUNCTION without doing any query?

Answer: In latest google sheets introduce a set of pre-defined conditional formatting rules. (Top navigation in google sheets, Format > Conditional format rule). But we highly recommend query is particularly important for advance condition statements to optimize your date sheet.

Question: Do we have a way to execute multiple operations without AND and OR logical operators?

Answer: You can use IFS Function to execute multiple conditions

That pretty much. You can get a complete idea of google sheets IF Function usage and how need to optimize with your datasheet.

Conclusion

Google sheets community rapidly growing & most of users are now highly recommend feature what is have. Google spread sheet development team always willing to introduce new features to make it easy. If FUNCTION is one most usable & valuable feature which they offer. There have so many helper formula and those are can be operate very easily with google sheets IF function.