IF function in Google Sheets: formula, IF with AND and OR, nested IF
IF returns one value when a condition is true and another when it is false. This guide covers the syntax, conditions on numbers, text, blanks and dates, IF with AND and OR, nested IF and IFS, IF across a column, and the mistakes that produce wrong results without an error.
The IF function checks a condition and returns one value when it is true and another when it is false:
=IF(B2 >= 50, "Pass", "Fail")
If the number in B2 is 50 or more, the formula returns Pass; otherwise it returns Fail.
| You need | Formula |
|---|---|
| Two conditions that both have to be true | =IF(AND(C3 <= 20, D3 > 30), "Bonus", "No Bonus") |
| Either of two conditions | =IF(OR(C3 <= 20, D3 > 30), "Bonus", "No Bonus") |
| Three or more outcomes | =IFS(D3 < 30, "Low", D3 <= 40, "Regular", TRUE, "High") |
| A check for an empty cell | =IF(D3 = "", "No Data", C3 * D3) |
| A condition on a date | =IF(D3 < DATE(2024, 1, 1), "Bonus", "No Bonus") |
| The same IF for a whole column | =ARRAYFORMULA(IF(D3:D9 > 30, "Bonus", "No Bonus")) |
IF function syntax
=IF(logical_expression, value_if_true, value_if_false)
- logical_expression: a test that is either TRUE or FALSE, usually a comparison with
=,<>,>,<,>=or<=. - value_if_true: what to return when the test is TRUE: a number, text in quotes, a cell reference or another formula.
- value_if_false (optional): what to return when the test is FALSE. If you leave it out, the formula returns FALSE in that case.
Text has to be in double quotes. =IF(D3 > 1000, Yes, No) returns #NAME?, because Sheets reads Yes and No as names of something it does not know.
IF with numbers, blanks and text
IF with numbers, blanks and text
A calculation as the result
The two results do not have to be labels. To pay a 15% bonus on the weekly salary to everyone who worked more than 30 hours:
=IF(D3 > 30, E3 * 0.15, 0)
Copy the formula down the column to apply it to every employee.
Empty cells
Compare the cell with an empty string to catch missing data before it turns into a wrong calculation:
=IF(D3 = "", "No Data", C3 * D3)
=IF(ISBLANK(D3), "No Data", C3 * D3) does the same for truly empty cells. The comparison with “” also catches cells that hold a formula returning an empty string, which ISBLANK does not.
Text
A text condition compares the cell with a string in quotes:
=IF(E3 = "No Data", "No Bonus", E3 * 0.15)
The comparison ignores case: “no data” and “No Data” are equal. For a case-sensitive test, use EXACT: =IF(EXACT(E3, "No Data"), ...).
The same test can return a status instead of a number:
=IF(E3 = "No Data", "Not paid", "Paid")
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Nested IF: more than two outcomes
Nested IF: more than two outcomes
For three or more outcomes, put another IF into the value_if_false of the first one. To label weekly hours as Low (under 30), Regular (30 to 40) or High (over 40):
=IF(D3 < 30, "Low", IF(D3 <= 40, "Regular", "High"))
The conditions are checked in order, and the first one that is true decides the result. That is why the second IF only needs D3 <= 40: it is reached only when D3 is 30 or more. The screenshot spells out a third IF for “High”; the short form above returns the same labels.
The IFS function writes the same logic as a flat list of condition and result pairs:
=IFS(D3 < 30, "Low", D3 <= 40, "Regular", TRUE, "High")
The final TRUE is the “everything else” case. Without it, IFS returns #N/A when no condition matches.
IF with AND and OR
AND: all conditions true
AND returns TRUE only when every condition inside it is true. To give a bonus to employees with an hourly rate of $20 or less who worked more than 30 hours:
=IF(AND(C3 <= 20, D3 > 30), "Bonus", "No Bonus")
OR: at least one condition true
OR returns TRUE when any of its conditions is true:
=IF(OR(C3 <= 20, D3 > 30), "Bonus", "No Bonus")
OR is also the way to test one cell against several values: =IF(OR(B3 = "North", B3 = "South"), "Domestic", "Export").
AND and OR together
The two can be nested. With the rate in column C, the hours in column D and the weekly salary in column E, this formula gives a bonus when the rate is below $20 and the hours are above 30, or when the salary is below $1,000:
=IF(OR(AND(C3 < 20, D3 > 30), E3 < 1000), "Bonus", "No Bonus")
Read it from the inside: AND covers the first rule, and OR adds the second one as an alternative.
IF with dates
Compare a date cell with a date built by the DATE function. To find the employees whose contract started before 2024:
=IF(D3 < DATE(2024, 1, 1), "Bonus", "No Bonus")
Do not write the date as text in the comparison. =IF(B3 = "1/5/2023", "yes", "no") returns “no” even when B3 holds that date, because a date is a number and the quoted value is text. For today’s date, use TODAY(): =IF(D3 < TODAY(), "Past", "Upcoming").
IF with other functions
ISTEXT and ISNUMBER: test the type of a cell
=IF(ISTEXT(E3), "Not paid", IF(ISNUMBER(E3), "Paid"))
- ISTEXT(E3): TRUE when the salary cell holds text such as “No Data”.
- ISNUMBER(E3): TRUE when it holds a number.
An empty cell is neither text nor a number, so this formula returns FALSE for it. Add a third value, or test for the empty cell first.
A whole column with ARRAYFORMULA
Inside ARRAYFORMULA, IF takes ranges and returns a result for every row:
=ARRAYFORMULA(IF(E3:E9 = "No Data", "No Bonus", E3:E9 * 0.15))
One formula in the first cell fills the column. All ranges in it have to cover the same rows. AND and OR do not work row by row inside ARRAYFORMULA; multiply the conditions for AND and add them for OR.
Conditional sums and counts
To add up the hours of employees whose contract started before 2024, IF can be combined with SUM:
=SUM(ARRAYFORMULA(IF(E3:E9 < DATE(2024, 1, 1), D3:D9, 0)))
IF returns the hours for the rows that meet the condition and 0 for the others, and SUM adds them. For a single condition, SUMIF is shorter and gives the same total: =SUMIF(E3:E9, "<" & DATE(2024, 1, 1), D3:D9).
IF can also react to a count. This formula checks whether more than five employees have entered their hours:
=IF(COUNT(D3:D9) > 5, "More than five employees", "Five or fewer employees")
COUNT counts the cells of D3:D9 that hold numbers. To count the cells that meet a condition, use COUNTIF.
Mistakes and errors in IF formulas
Mistakes and errors in IF formulas
⚠️ Error: The formula returns FALSE.
✅ Solution: The condition is false and there is no third argument. Add the value to return in that case, or "" to leave the cell empty.
⚠️ Error: #NAME?
✅ Solution: A text value is not in quotes, or a function name is misspelled.
⚠️ Error: The comparison with a number gives the wrong answer.
✅ Solution: The cell holds the number as text. Text counts as greater than any number, so ="10" > 5 is TRUE, and so is ="1" > 5. Convert the cell with VALUE or fix the source data.
⚠️ Error: #VALUE!
✅ Solution: The first argument is text that is not a condition, for example =IF("abc", ...). The test has to be a comparison or a value that is TRUE, FALSE or a number.
⚠️ Error: #DIV/0! or #N/A inside the IF.
✅ Solution: An error in the condition or in the returned value passes through IF. Fix the cause, or catch it with IFERROR or IFNA around the part that produces it: =IF(IFERROR(A2 / B2, 0) > 1, "Above", "Below").
⚠️ Error: A nested IF returns the wrong label.
✅ Solution: The conditions are in the wrong order. The first true condition wins, so start with the narrowest one, or rewrite the formula with IFS.
Related Google Sheets functions
Related Google Sheets functions
Functions that are often used with or instead of IF, each with its own guide:
- IFS: Several conditions and results in one flat formula.
- VLOOKUP with IF: Test the value a lookup returns, or choose the table to search.
- COUNTIF and COUNTIFS: Count the cells that meet conditions.
- SUM, SUMIF and SUMIFS: Add up the cells that meet conditions.
- FILTER: Returns the rows that meet a condition.
- QUERY with UNIQUE: Combines SQL-like filtering with deduplication to return only the distinct rows that match a condition.
- Data validation: Restricts what can be entered in a cell with rules and drop-down lists.
- Pivot tables: Summarize a table by category without formulas.
Business rules written as IF formulas need a single home
Business rules written as IF formulas need a single home
An IF formula is often a business rule in disguise: what counts as a qualified lead, which orders are “large”, when a campaign is “active”. Typed into one spreadsheet, the rule lives in that file only. The next report gets its own copy, with a slightly different threshold, and the two no longer agree.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. A rule that is defined there once reaches every sheet as a ready column, the same in each report, and IF is left for the logic that belongs to one sheet. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
How do I create nested IF statements for complex conditions in Google Sheets?
To handle more complex conditions in Google Sheets, you can create nested IF statements by placing additional IF functions inside the value_if_true or value_if_false parts of another IF. This lets you check multiple criteria in one formula and return different results based on each condition—ideal for more detailed analysis and decision-making.
How can I apply conditional formatting using the IF function in Google Sheets?
Select the range, open Format > Conditional formatting, choose Custom formula is and enter the condition itself, for example =$A1 > 100. No IF is needed: the rule applies the format wherever the condition is TRUE. The dollar sign fixes the column, so the whole row is formatted.
Can I use the IF function to identify blank or non-blank cells?
Yes, you can use the IF function in Google Sheets to identify blank or non-blank cells. For instance, =IF(ISBLANK(A1), "Blank", "Not Blank") checks if cell A1 is empty and returns "Blank" if true, otherwise "Not Blank". This method helps manage data effectively based on cell content presence.
How can I use the IF function to check for numerical values in Google Sheets?
You can use the IF function in Google Sheets to check for numerical values by combining it with the ISNUMBER function. For example, =IF(ISNUMBER(A1), "Numeric", "Not Numeric") checks if cell A1 contains a numerical value and returns "Numeric" if true, otherwise "Not Numeric".
What is the syntax of the IF function in Google Sheets?
The IF function in Google Sheets allows you to execute logic based on whether a specific condition is met. Here’s the syntax for the IF function:
> `=IF(logical_expression, value_if_true, value_if_false)`
Here:
* logical_expression: a logical expression that Google Sheets evaluates. It must return either TRUE or FALSE. * value_if_true: the result if the condition is met. * value_if_false: the result if the condition is not met.
