How to Create an IF-THEN Formula in Excel: Basic Syntax, Multiple Conditions, & More

One of the most useful features of Microsoft Excel is the IF-THEN statement, which is used to perform calculations or display text based on whether a condition is true. If you're confused about the IF function, this handy wikiHow tutorial will guide you through writing useful IF-THEN formulas, from making simple comparisons to testing multiple conditions. Read on for examples, troubleshooting tips, and more!

Section 1 of 4:

IF-THEN Syntax

  1. Image titled How Do You Write an if then Formula in Excel Step 1
    =IF(logical_test, value_if_true, value_if_false). An IF statement makes a logical comparison based on whether a condition is true or false.
    • logical_test: This is the condition that you want to test (required).
    • value_if_true: The value you want returned if logical_test is true (required).
    • value_if_false: The value you want returned if logical_test is not true (optional).
    2. Advertisement
Section 2 of 4:

Simple Examples

  1. Image titled How Do You Write an if then Formula in Excel Step 2
    1
    =IF(B3>C3, "Goal Met", "Goal Not Met").
    • In this example, the condition is B3>C3, meaning "If the value of B3 is greater than C3."
    • If the value of B3 is greater than C3, then Goal Met will appear in the cell.
    • If the value of B3 is less than the value of C3, then Goal Not Met will appear in the cell.
  2. Image titled How Do You Write an if then Formula in Excel Step 3
    2
    =IF(B3=C3, "Goal Met").
    • In this example, the condition is B3=C3, meaning, "If the value of B3 is equal to the value of C3."
    • If the values are equal, "Goal Met" will appear in the cell.
    • If the values are not equal, a zero will appear in the field because we did not define a third (ELSE) value.[1]
  3. Image titled How Do You Write an if then Formula in Excel Step 4
    3
    =IF(B3*2>C3, C3*400, "Good").
    • In this example the condition is B3*2>C3, meaning "If the value of B3 multiplied by 2 is greater than the value of C3."
    • If the value of B3*2 is greater than the value of C3, the value of C3 multiplied by 400 will appear in this cell.
    • If the value of B3*2 is not greater than the value of C3, the word "Good" will appear in the cell.
  4. Image titled How Do You Write an if then Formula in Excel Step 5
    4
    =IF(B3="Sold", "1", "")
    • In this example, the condition is B3="Sold", meaning "If the value of cell B3 contains the word Sold."
    • If B3 contains the word "Sold," a "1" will appear in the cell.
    • If B3 says anything other than the word "Sold," the condition is false. Since our ELSE value is " ", which is two double quote marks with nothing in between, the cell value will be blank.
  5. Image titled How Do You Write an if then Formula in Excel Step 6
    5
    =IF(D3="Taxed", F3*.07, "0").
    • In this example, the condition is D3="Taxed", meaning "If the value of D3 is the word Taxed."
    • If D3 contains the word "Taxed," the result will be the value of F3 multiplied by .07.
    • If D3 contains anything other than the word "Taxed," the result will be 0.
  6. Image titled How Do You Write an if then Formula in Excel Step 7
    6
    Things to remember.
    • An IF statement can have two different results—one result if the condition is true, and another result if the condition is false.
    • The ELSE value, which is what will happen if the result is false, is optional—if you don't specify what to place in the cell if the condition is false, a "0" will appear in the cell. If you'd rather the cell be blank, make your ELSE value "".
    • When referring to specific text in a condition or THEN/ELSE value, always surround that text in quotation marks.
    7. Advertisement
Section 3 of 4:

Testing Multiple Conditions

  1. Image titled How Do You Write an if then Formula in Excel Step 8
    1
    You can nest up to 64 IF/THEN statements in a single formula. For example, let's say you want to create a formula that calculates sales tax for purchases made from Alaska, California, Oregon, and Washington. Since each of these four states has its own tax rate, we'll need to create a formula that contains four IF/THEN statements to test four conditions. Assuming the sale amount is in G1 and the purchaser's state is in F1, our formula would look like this, using the 2022 tax rates for these 4 states:
    • =IF(F1="Alaska", G1*0,IF(F1="California",G1*0.0725,IF(F1="Oregon",G1*0,IF(F1="Washington", G1*0.065))))
  2. Image titled How Do You Write an if then Formula in Excel Step 9
    2
    Nesting many IF/THEN statements can be challenging. Aside from the very long formulas, creating very long IF/THEN statements may become cumbersome if you have to frequently change them. In our previous example, we'd need to update our IF/THEN statements any time a state's tax rate changed.
    • A good alternative to IF/THEN statements in this situation would be to add the values to a single table and use a VLOOKUP formula to query the table for the proper rates.
    • For example, if we add a list of our states to column A and their corresponding tax rates in column B, we could write a VLOOKUP formula that does the math for us without specifying the amounts in the formula itself. The shortened version of the above nested IF/THEN formula would be =G1*VLOOKUP(F1,A:B,2,TRUE).
    • If you had to update a tax rate, you'd now only have to update it in the referenced table, not all of your formulas.
    3. Advertisement
Section 4 of 4:

Troubleshooting

  1. Image titled How Do You Write an if then Formula in Excel Step 10
    1
    The result is zero (0). This means that you didn't specify a value for either the value_if_true or value_if_false arguments.[2]
    • If you don't specify a value_if_false value, the result will always be 0 if the condition is false.
  2. Image titled How Do You Write an if then Formula in Excel Step 11
    2
    The result is the #NAME? error. This indicates that a syntax error—make sure you've surrounded the formula after = with parentheses, your arguments are separated by commas, and that any plain text is surrounded by quotation marks.
    • For example, this IF/THEN statement will result in the #NAME? error because there are no quotation marks around the value OK: =IF(A2>1,OK).
    • The correct syntax would be =IF(A2>1,"OK")
    3. Advertisement

You Might Also Like

Google Sheets How to Insert Time in Cell Automatically4 Easy Ways to Add the Time and Date Automatically in Sheets
Use Google SpreadsheetsHow to Use Google Sheets: The Ultimate Guide for Getting Started
Add a Custom Field in Pivot TableHow to Add Custom Calculated Fields to Pivot Tables in Excel
Add the Same Value to Multiple Cells in Excel
How to
Add the Same Value to Multiple Cells in Excel
Add Two Cells Containing the Sum of Other Formulas in Excel
How to
Add Two Cells Containing the Sum of Other Formulas in Excel
Create a Form in a Spreadsheet
How to
Create a Form in a Spreadsheet
Add Rows to a Pivot Table
How to
Add Rows to a Pivot Table
Show the Max Value in an Excel Graph
How to
Show the Max Value in an Excel Graph
Create a Formula to Increase a Date by 1 Month
How to
Create a Formula to Increase a Date by 1 Month
Change X Axis Scale in ExcelHow to Change the X Axis Scale in an Excel Chart
Change an Excel Sheet from Read Only
How to
Change an Excel Sheet from Read Only
Make a Shared Spreadsheet
How to
Make a Shared Spreadsheet
Sort by Number on Google Sheets on PC or Mac
How to
Sort by Number on Google Sheets on PC or Mac
Make a Google SpreadsheetHow to Make a Google Spreadsheet: Beginners Guide
Advertisement

References

  1. ↑ https://support.microsoft.com/en-us/office/if-function-nested-formulas-and-avoiding-pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8
  2. ↑ https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

About This Article

Nicole Levine, MFA
Written by:
Nicole Levine, MFA
wikiHow Technology Writer
This article was co-authored by wikiHow staff writer, Nicole Levine, MFA. Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions. This article has been viewed 45,132 times.
How helpful is this?
Co-authors: 4
Updated: April 18, 2022
Views: 45,132
Categories: Spreadsheets
Article SummaryX

1. The syntax for an IF-THEN statement is =IF(logical_test, value_if_true, value_if_false).
2. "logical_test" is the condition you want to test, such as whether a cell value is greater than or equal to another cell.
3. "value_if_true" is what the formula should do if the test condition is true.
4. "value_if_false" is what the formula should do if the test condition is "not" true.

Did this summary help you?
Advertisement