4.1 Conditional Functions

Conditional functionsCalculations where the cell references used to complete the calculations depend on the values of other cells in a worksheet. allow you to perform calculations where the cell references used to complete the calculations depend on the values of other cells in a worksheet. We will discuss three of these.

The first conditional function is COUNTIF. You will recall the COUNT function from the last unit of the class. The COUNT function is used to determine the number of cells that contain numbers in a range of cells. The COUNTIF function allows you to determine the number of cells within a range of cells that contain a specific value. Figure 4.1 demonstrates the use of the COUNTIF function. The syntax for the COUNTIF function is below. The first argument of the COUNTIF function is the range of cells within which you want Excel to count the number of matches. The second argument defines the specific value that you would like Excel to match to the cells in the range.

=COUNTIF(range,criteria)

The "range" argument will always be a reference to a cell or a range of cells. This reference can contain either relative or absolute cell references. If you plan to reuse the COUNTIF function by copying and pasting the function to other cells in a worksheet, you should carefully construct the reference in the "range" argument so that the reference points to the correct range of cells when the function is pasted in a new cell. This often requires an absolute cell reference.

The "criteria" argument of the COUNTIF function can accept specific values such as numbers, words or phrases (in quotations), or dates. The "criteria" argument may also be a reference to another cell. In this case, the value stored in the referenced cell will be used to find matches.

The SUMIF function is used to calculate the total for a set of values that match a specific criterion. The syntax of the SUMIF function is below. The first two arguments of the SUMIF function are similar to the COUNTIF function. The first argument is the range of cells within which you want Excel to search for a match to the criterion you set. The second argument defines the criterion used to find matches. The last argument defines the range of cells to be included when the total is calculated. The ranges described in the first and third arguments of the SUMIF function must be the same size. Excel will pair the cells in two ranges, based on the relative positions of the cells within the ranges, so that the appropriate cells in the "sum_range" are included in the calculation. Figure 4.2 demonstrates the use of the SUMIF function.

=SUMIF(range,criteria,[sum_range])

Assume you own a small car dealership and are interested in determining the total value of the white, black, red, and blue cars on your lot. The first argument of the SUMIF function depicted in Figure 4.2 defines the range within which Excel will search for matches. In this case, you want Excel to match car color in range A2:A20. The second argument defines the specific value you wish to use to find a match. In cell E3, you want to calculate the total value of the white cars in your inventory. The cell D3 contains the value "White" and may be used as the second argument in the SUMIF function in cell E3. The final argument for the SUMIF function is the "sum_range" In this case, the values you wish to total together are found in the range B2:B20. Notice that the absolute references used in the first and third arguments of the SUMIF function will allow you to copy and paste the formula in cell E2 to cells E3 through E5 without adjusting the "range" and "sum_range" arguments. The use of a relative reference as the second argument allows the criterion to adjust when the formula is pasted down to cells E3 through E5.

When the SUMIF function described in Figure 4.2 is executed, Excel will search for all of the white cars in range A2:A20. Once the white cars are found, Excel will use the corresponding cells in the range B2:B20 to calculate the total inventory value. Only those values in cells B2:B20 that pair with the matched values in the range A2:A20 will be included when calculating the total.

The AVERAGEIF function is similar to the SUMIF function. The AVERAGEIF function is used to calculate the average for a set of values that match a specific criterion. The first two arguments for the AVERAGEIF function are the same as the SUMIF function. The third argument is used to define the range of cells that contain the values to be included in the average calculation. The syntax for the AVERAGEIF function is below:

=AVERAGEIF(range,criteria,[average_range])

Figure 4.3 depicts a situation similar to that portrayed in Figure 4.2. Instead of calculating the total inventory value for each color of car, you want to calculate the average value for each car color. Notice that the arguments used in the SUMIF function for Figure 4.2 are the same as the arguments used for the AVERAGEIF function in Figure 4.3.

Conditional functions are often used to summarize information in a large data table. If the data can be segmented into meaningful groups, the conditional functions can be used to easily calculate basic summary statistics for each of the groups. This is the case with the practice problems for this section.