Conditional Functions

Conditional functions perform calculations on a cell or range of cells only if those cells meet a certain condition. These functions test a given range and determine if the condition is true or false before continuing. A condition can be any relational comparison:

Examples:

• A3>14 Whether the valueof cell A3 is larger than the number 14
• D5<=2 Whether the value of cell D5 isless than or equal to the number 2
• T47=”cheese” Whether the value of cell T47 is “cheese”

There are six types of conditional functions covered by this course. Each has a specific use and syntax.

AND and OR

=AND(condition1, [condition2], …)
=OR(condition1, [condition2], …)

AND/OR will return either “TRUE” or “FALSE”.

AND will evaluate to TRUE only if all conditions are true.

For example,

=AND(G2=”BLAH”,A2=”OCHO”)

will evaluate to TRUE if the cell G2 is “BLAH” and the cell A2 is “OCHO”.

OR will evaluate to TRUE if any of the conditions are true.

For example,

=OR(E2>12,F2<54)

evaluates to TRUE if E2>12, or F2<54, or if both of the conditions are true!   

IF

=if(condition,value_if_true,value_if_false)

If the given condition is true, the cell will be set to the “value_if_true.” If the condition is false, the cell will be set to the “value_if_false.” The “value_if_true” and “value_if_false” are optional inputs which default to TRUE and FALSE, respectively.

Example:

=if(A2>3,32,”Number too small”)

Reads: If A2>3 is true, then set the cell to 32. Otherwise, set the cell to “Number too small”

Nested IF Functions

You can nest one “if” function inside of another to test for multiple conditions at once. A single “if” function, without the use of any other functions, can only test for a single condition.

Example:

Test the condition of whether the contents of cell A2 are between 10 and 20.

=if (A2>20,”Number too large”, if (A2<10,”Number too small”, “Just right!”))

The completed function will test whether the value of A2 is larger than 20. If it is, the active cell will be set to “Number too large.” If A2 is less than 20, it will be tested to see if the value of A2 is less than 10. If it is, the active cell is set to “Number too small.” Otherwise, we know that the value of A2 must be between 10 and 20, so the active cell is set to “Just right!”

COUNTIF

=countif(range,criteria)

The “countif” function will increment the active cell by one each time the criteria is true for a cell in the given range.

Example:
You want to know how many universities have “red” as one of their school colors, and have compiled a database of schools and their colors in Excel.

=countif (F1:F230, “=red”)

If a cell in F1:F230 satisfies “=red”, then increment the active cell by one.

SUMIF

=sumif(range,criteria,sum_range)

The “sumif” function utilizes a range and a corresponding sum_range of the same size. Each time the criteria is true, the active cell will be incremented by the value in the corresponding cell in the sum_range.  If “sum_range” is not specified, excel will treat “range” as “sum_range” as well.

Example:
You wish to know how many hours you have spent watching television, from a database of your time spent on various activities.

=sumif (D1:D230,”=television”, H1:H230)

If a cell in D1:D230 satisfies “=television”, then increment the active cell by the corresponding cell from H1:H230. If cell D7contains the word “television,” the active cell will be incremented from the corresponding cell from H1:H230, or cell H7.