Using the IF Function

Contact Us or call 1-877-932-8228
Using the IF Function

Using the IF Function

The IF function can be used to execute formulas only under certain conditions or to execute different formulas based on specified conditions. To use the IF function, you need to know:

  1. Logical Test. This is simply the thing you want to test. For example:
    1. If the number is greater than 10, then...
    2. If the value is "blue", then...
  2. Value if True. This is the value to return if the requirement is met (the logical test is true).
  3. Value if False. This is the value to return if the requirement is not met (the logical test is false).

Here are some things to know about the IF function:

  1. In plain English, the IF function says: If X condition is true, put Y value in this cell; otherwise, put Z value in the cell.
  2. The value returned by the IF function can be a number, text, a formula, or a reference to another cell.
  3. Enter "" (open and close quotes) if you do not wish to return a value.
  4. You can test up to seven conditions by nesting IF functions within the original IF function. Here is an example in which nested IF functions are used to return grades:

To use the IF function:

  1. On the FORMULAS tab, in the Function Library group, click the Insert Function command:
  2. In the Insert Function dialog box:
    1. Search on "IF" or, in the Or select a category drop-down box, select Logical.
    2. Under Select a function, select IF.
    3. Click OK.
  3. In the Function Arguments dialog box:
    1. Enter the logical test (e.g., B2>10, B2<C2, B2="Blue").
    2. Type in the value if true.
    3. Type in the value if false.
    4. Click OK.

Note that when you see a small picture of a worksheet with a red arrow on it next to a data entry field, you can click this image to select a cell, rather than typing the cell's location into the data entry field. For example:

  1. Click the circled image:
  2. The Function Arguments dialog box opens up. Click a cell and the cell's location appears in the Function Arguments dialog box. Click the image to the far right of the Function Arguments dialog box to return to the previous dialog box:
  3. Note that in the original dialog box, the selected cell's location has been added into the data entry field:

This is especially useful when referring to cells on a separate worksheet.

Here are some examples of the IF statement in use:

  1. =IF(A1=B1,"Same","Different"):
  2. =IF(A1="Blue",B1,C1):
  3. =IF(A1>100,"Victory!","Try again."):

Using the SUMIF, AVERAGEIF, and COUNTIF Functions

SUMIF

There are a few variations of the IF function that may be useful to be aware of when working with Excel.

The SUMIF function is a variation of the IF function, which allows you to specify criteria for a sum. For example, you may want to sum only the numbers in a column that are above 100.

To use SUMIF, you need to know:

  1. range. The range of cells to which you want to apply the criteria.
  2. criteria. This can be text, numbers, a function, or an expression. For example, the criteria could be > 100.

AVERAGEIF

AVERAGEIF does what it sounds like: it averages a range of cells. For example, you could average students' grades in a spreadsheet.

To use AVERAGEIF, you need to know:

  1. range. Enter a range of at least two cells to which to apply the criteria.
  2. criteria. The criteria is defines what is to be averaged, such as numbers, expressions, and text.

COUNTIF

The COUNTIF function allows you to count the number of cells in a range that meet the criteria you specify. For example, you can count the number of students who received As.

To use the COUNTIF function, you need to know:

  1. range. Enter a range of at least two cells to which to apply the criteria.
  2. criteria. The criteria is defines what is to be averaged, such as numbers, expressions, and text.

Using AND/OR Functions

The AND and OR functions are similar to the IF function in that they are logical functions.

The syntax of the AND function is: =AND(logical1,logical2, ...). It returns TRUE if all arguments are true.

The syntax of the OR function is: =OR(logical1,logical2, ...). It returns TRUE if any arguments are true.

Next