Excel Training: Perform Operations with Formulas and Functions

Using SUMIF and SUMIFS

At this point, you should be able to use SUM to total a range of cells and use SUMPRODUCT to total the product of two cell ranges, but what if you want to sum together only cells that meet specific criteria? You could filter the data and then use SUM, but it is more efficient to use the SUMIF function. The SUMIF function totals the values only where cells meet the specified criteria. SUMIF takes two required arguments and one optional argument: Range—the range of cells to evaluate against the criteria Criteria—the conditions (the criteria) the cell must meet in order to be included in the total, and optionally Sum_range—(optional) the range of cells containing the values to be summed. Sum_range must be the same size as Range. This argument is necessary only when the values to be summed are not the same as the values to be evaluated against the criteria. The formula in Figure EX 6.9 uses SUMIF to calculate the total number of items ordered for the Item ID specified in cell B1. SUMIF evaluates the values in the JuneItems range (the Item ID column). If the value matches the criteria specified in cell B1, then the corresponding value in the JuneOrdered range (the Ordered column) is included in the total. =SUMIF(Range,Criteria,[Sum_range]) =SUMIF(JuneItems,B1,JuneOrdered) To create a formula using SUMIFS: Select the cell where you want to enter the formula. On the Formulas tab, in the Function Library group, click the Math & Trig button, and select SUMIF to open the Function Arguments dialog. In the Range argument box, enter the cell range or named range to evaluate against the criteria. In the Criteria argument box, enter the criteria. The criteria can be a text string, numerical value, expression, or cell reference. Text strings and expressions must be enclosed in quotation marks. In the Sum_range argument box, enter the cell range or named range containing the values you want to add together. If you omit this argument, SUMIF will total the values in the Range argument instead. Click OK. To sum data that meet multiple criteria, use SUMIFS. SUMIFS takes arguments for up to 127 pairs of criteria ranges and criteria. The result of SUMIFS is the total of all the cells that meet all criteria. SUMIFS takes three required arguments and multiple optional arguments: Sum_range—the range of cells to sum where criteria are met Criteria_range1—the range of cells containing the values to be evaluated against the first criteria Criteria1—the first criteria Criteria_range2—(optional) the range of cells containing the values to be evaluated against the second criteria Criteria2—(optional) the second criteria (continuing up to Criteria_range127 and Criteria127) The formula in Figure EX 6.11 uses SUMIFS to calculate the sum of the values in the Ordered column (the range JuneOrdered) where the Item ID (the range JuneItems) meets the criteria specified in cell B1 and the PO # (the range JunePOs) meets the criteria specified in cell B2. =SUMIFS(Sum_range,Criteria_range1,Criteria1,[Criteria_range2],[Criteria2]...) =SUMIFS(JuneOrdered,JuneItems,B1,JunePOs,B2) To create a formula using SUMIFS: Select the cell where you want to enter the formula. On the Formulas tab, in the Function Library group, click the Math & Trig button and select SUMIFS to open the Function Arguments dialog. In the Sum_range argument box, enter the cell range or range name containing the values to be summed if all the criteria are met. In the Criteria_range1 box, enter the cell range or range name containing the values to be evaluated against the first criteria. Text strings and expressions must be enclosed in quotation marks. In the Criteria1 argument box, enter the text string, number, expression, or cell reference for the first criteria. In the Criteria_range2 box, enter the cell range or range name containing the values to be evaluated against the second criteria. In the Criteria2 argument box, enter the text string, number, expression, or cell reference for the second criteria. Text strings and expressions must be enclosed in quotation marks. Continue entering the criteria range and criteria pairs until you are finished. Click OK.

Understanding Absolute and Relative References

A cell's address, its position in the workbook, is referred to as a cell reference when it is used in a formula. In Excel, the $ character before a letter or number in the cell address means that part of the cell's address is absolute (nonchanging). Cell references can be relative, absolute, or mixed. A relative reference is a cell reference that adjusts to the new location in the worksheet when the formula is copied. An absolute reference is a cell reference whose location remains constant when the formula is copied. A mixed reference is a combination cell reference with a row position that stays constant with a changing column position (or vice versa). Relative reference—A1 Absolute reference—$A$1 Mixed reference with absolute row—A$1 Mixed reference with absolute column—$A1 Here's how relative and absolute references work: When you type a formula into a cell, it uses relative references by default. Excel notes the position of the referenced cell relative to the active cell. For example, if cell B19 is the active cell and you type the formula =B15, Excel displays the value of the cell that is up four rows from the active cell. If you change the structure of the worksheet by adding or removing rows or columns, Excel will automatically update all relative cell references. In this example, Excel will update the formula to reflect the new address of the cell that is up four rows from the cell containing the formula. If you copy the formula =B15 from cell B19 and paste it into cell C19, the pasted formula will update automatically to =C15 to reflect the cell address that is up four rows from the pasted formula. But what if you don't want the cell reference to adjust? For example, in Figure EX 1.20 cell J21 contains a value that you want to use in calculations for multiple cells in a row. If you were to copy the formula =B20*J21 from cell B21 to cell C21, the formula would update to =C20*K21 (not what you intended) because both of the cell references are relative. Instead, you want the reference to cell J21 to be absolute, so it does not update when you copy it. If you use the formula =B20*$J$21 instead and copy it from cell B21 to cell C21, the pasted formula will update only the relative reference B20. The absolute reference $J$21 will remain constant. The formula in cell C21 will be =C20*$J$21.

Entering Simple Formulas

A formula is an equation used to calculate a value. A formula can perform a mathematical calculation, such as displaying the sum of 35 + 47, or a formula can calculate a value using cell references, such as displaying a value equal to the value of another cell (=B15) or calculating an equation based on values in multiple cells (=B17+B18-B19). In Figure EX 1.17, cell B19 contains the formula =B15. This formula directs Excel to make the value of cell B19 equal to the value of cell B15. If the value in cell B15 changes, the value displayed in cell B19 will update accordingly. Notice that when the cell is selected, the result of the formula is displayed in the cell, while the formula is displayed in the formula bar. To enter a formula: Click the cell in which you want to enter the formula. Press = and begin typing the formula. The = tells Excel that you are entering a formula, not standard text or numeric data. Use the following symbols for mathematical operations: Table has 2 Columns Addition + Subtraction - Multiplication * Division / To add a cell reference to a formula, you can type the cell address or click the cell. If you are in the middle of typing a formula and you click another cell in the worksheet, Excel knows to add that cell reference to the formula instead of moving to it. Press Enter or click the Enter button to the left of the formula bar when you are finished entering the formula. When you edit the formula in the cell or the formula bar, any referenced cells are highlighted in the same color as the cell reference in the formula. When you have a formula with multiple cell references, such as the one in Figure EX 1.18, the colors make it easier to troubleshoot any errors.

Using Functions in Formulas

Functions are preprogrammed shortcuts for calculating equations. Functions can simplify a straightforward computation such as figuring the total of a list of values. They can also calculate the answer to a complicated equation such as figuring the monthly payment amount for a loan. Most functions require you to provide input called the arguments. For example, when writing a formula using the SUM function to calculate the total of a list of values, each value or range of values to be included in the calculation is an argument. Multiple arguments are separated by commas [,]. This formula will calculate the total of the values in cells B9 through B14: SUM(B9, B10, B11, B12, B13, B14) In this example, each cell reference is an argument. An easier way to write the arguments for this formula is: SUM(B9:B14) In the second example, the function requires only one argument—the cell range containing the values. Both formulas will return the same total value. The easiest way to enter a formula using a simple function like SUM is to type the formula directly in the cell or the formula bar. Begin the formula by typing =, and then type the function name. After the function name, type ( followed by the function arguments, separated by commas, and then ). Press Enter to complete the formula.

Formatting Text Using Functions

Functions can do more than perform calculations. Excel includes a special group of functions to modify text. These text functions are useful for ensuring that text data have a consistent appearance. In functions, text is referred to as a string or text string. Commonly used text functions are: PROPER—Converts the text string to proper case (the first letter in each word is capitalized). A formula using the PROPER function looks like this: =PROPER(D2) UPPER—Converts the text string to all uppercase letters. A formula using the UPPER function looks like this: =UPPER(F2) LOWER—Converts the text string to all lowercase letters. A formula using the LOWER function looks like this: =LOWER(H2) To create a formula with PROPER, UPPER, or LOWER, use either of these methods: If you prefer typing in the cell or the formula bar, try using Formula AutoComplete. If you prefer using the Function Arguments dialog, on the Formulas tab, in the Function Library group, click the Text button and click PROPER, UPPER, or LOWER.

Using AutoSum to Insert a SUM function

If your spreadsheet includes numerical data organized in rows or columns, AutoSum can enter totals for you. When you use AutoSum, Excel enters the SUM function arguments using the most likely range of cells based on the structure of your worksheet. For example, if you use AutoSum at the bottom of a column of values, Excel will assume that you want to use the values in the column as the function arguments. If you use AutoSum at the end of a row of values, Excel will use the values in the row. To insert a SUM function using AutoSum: Select the cell in which you want to enter the function. On the Home tab, in the Editing group, click the AutoSum button. Excel automatically inserts a formula with the SUM function, using the range of cells contiguous to (next to) the selected cell as the arguments for the function. You can increase or decrease the range of cells selected by clicking and dragging the corner of the highlighted cell range. Press Enter to accept the formula.

Finding Minimum and Maximum Values

In addition to AVERAGE, there are a few other statistical functions you may find useful in working with day-to-day spreadsheets. The MAX (maximum) statistical function will give you the largest value in a range of values. A formula using the MAX function looks like this: =MAX(A3:A6) The MIN (minimum) statistical function will give you the smallest value in a range of values. A formula using the MIN function looks like this: =MIN(A3:A6) To create a formula with the MAX or MIN function, use any of these methods: If you prefer typing in the cell or the formula bar, try using Formula AutoComplete. If the data are organized in rows or columns, you can use AutoSum. On the Home tab, Editing group or on the Formula tab, Function Library group, click the AutoSum button arrow and select Max or Min. If you prefer using the Function Arguments dialog, on the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and click MAX or MIN.

Calculating Averages

The AVERAGE statistical function is used to calculate the average value of a group of values. Average is calculated by adding the values, and then dividing the sum by the number of values. A formula using the AVERAGE function looks like this: =AVERAGE(B12:D12) The result of this formula is the sum of the values in cells B12:D12 divided by the number of values in that cell range. To create a formula with the AVERAGE function, use any of these methods: If you prefer typing in the cell or the formula bar, try using Formula AutoComplete. If the data are organized in rows or columns, you can use AutoSum. On the Home tab, Editing group or on the Formula tab, Function Library group, click the AutoSum button arrow and select Average. If you want to calculate the average for multiple rows or columns at once, use one of the average options in the Quick Analysis tool, Totals tab. If you prefer using the Function Arguments dialog, on the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and click AVERAGE.

Using the Logical Function IF

The IF logical function returns one value if a condition is true and another value if the condition is false. The IF function can return a numerical value or display a text string. The formula in Figure 3.27 uses the IF function to determine whether or not an item should be ordered. If the value of cell D2 (the quantity in stock) is greater than the value of cell E2 (the reorder level), the formula will return "do not order". If the value of cell D2 is not greater than the value of cell E2, the formula will return "order". The formula looks like this: =IF(D5>E5,"do not order","order") In Figure EX 3.27, the value of cell D5 is not greater than the value of cell E5, so the IF function returns the Value_if_false argument displaying the word order. In the next row in the worksheet, the value of cell D6 is greater than the value of cell E6, so the IF function returns the Value_if_true argument displaying the phrase do not order. To create a formula using the IF function: Select the cell where you want to enter the formula. On the Formulas tab, in the Function Library group, click the Logical button. Select IF to open the Function Arguments dialog. IF functions take three arguments as shown in the Function Arguments dialog in Figure EX 3.28. Enter the Logical_test argument. This argument states the condition you want to test for. The Logical_test always includes a comparison operator (=, >, <, etc.). Enter the Value_if_true argument. This argument is the text string or value that will be displayed or the formula that will be calculated if the Logical_test argument is true. Enter the Value_if_false argument. This argument is the text string or value that will be displayed or the formula that will be calculated if the Logical_test argument is false. Click OK.

Creating Formulas Using Counting Functions

The counting functions are useful when you need to know how many numbers or items are in a list or how many rows are missing data for a particular column. COUNT—Counts the number of cells that contain numbers within a specified range of cells. A formula using the COUNT function looks like this: =COUNT(A7:A20) The result of this formula is the number of cells in A7 through A20 that contain numerical values. If you want to include cells that contain text, use COUNTA instead. To count numbers, you have two additional options: If the data are organized in rows or columns, you can use AutoSum to enter the COUNT function. On the Home tab, Editing group or on the Formula tab, Function Library group, click the AutoSum button arrow and select Count Numbers. If you want to count the numbers in multiple rows or columns at once, use one of the Count options from the Quick Analysis tool, Totals tab. COUNTA—Counts the number of cells that are not blank within a specified range of cells. Use COUNTA if your cell range includes text data or a mix of text and numbers. A formula using the COUNTA function looks like this: =COUNTA(B7:B20) The result of this formula is the number of cells in B7 through B20 that contain any data (numerical or text). COUNTBLANK—Counts the number of empty (blank) cells within a specified range of cells. Cells that contain a zero (0) or a formula that results in zero are not considered blank. Use COUNTBLANK to find the number of rows missing values in a column. A formula using the COUNTBLANK function looks like this: =COUNTBLANK(E7:E20) The result of this formula is the number of cells in E7 through E20 that are empty. To create a formula with COUNT, COUNTA, or COUNTBLANK, use either of these methods: If you prefer typing in the cell or the formula bar, try using Formula AutoComplete. If you prefer using the Function Arguments dialog, on the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and click COUNT, COUNTA, or COUNTBLANK.

Using AVERAGEIF and AVERAGEIFS

The statistical functions AVERAGEIF and AVERAGEIFS are similar to SUMIF and SUMIFS. They calculate the average value where cells meet specified criteria. The AVERAGEIF takes two required arguments and one optional argument: Range—the range of cells to evaluate against the criteria Criteria—the conditions (the criteria) the cell must meet in order to be included in the total, and optionally Average_range—(optional) the range of cells containing the values to be averaged. Average_range must be the same size as Range. This argument is necessary only when the values to be averaged are not the same as the values to be evaluated against the criteria. The formula in Figure EX 6.13 uses AVERAGEIF to calculate the average delivery time for items currently on order. AVERAGEIF evaluates the values in the ReorderStatus range (the On Order? column). If the value matches the criteria specified (yes), then the corresponding value in the DeliveryTime range (the Delivery Time in Days column) is included in the range of values to be averaged. Note that the text criteria yes must be enclosed in quotation marks. =AVERAGEIF(Range,Criteria,[Average_range]) =AVERAGEIF(ReorderStatus,"yes",DeliveryTime) To create a formula using AVERAGEIF: Select the cell where you want to enter the formula. On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select AVERAGEIF to open the Function Arguments dialog. In the Range argument box, enter the cell range or named range to evaluate against the criteria. In the Criteria argument box, enter the criteria. The criteria can be a text string, numerical value, expression, or cell reference. Remember, text strings and expressions must be enclosed in quotation marks. In the Average_range argument box, enter the cell range or named range containing the values you want to average. If you omit this argument, AVERAGEIF will average the values in the Range argument instead. Click OK. To average data that meet multiple criteria, use AVERAGEIFS. AVERAGEIFS takes arguments for up to 127 pairs of criteria ranges and criteria. The result of AVERAGEIFS is the average of all the cells that meet all criteria. AVERAGEIFS takes three required arguments and multiple optional arguments: Average_range—the range of cells to average where criteria are met Criteria_range1—the range of cells containing the values to be evaluated against the first criteria Criteria1—the first criteria Criteria_range2—(optional) the range of cells containing the values to be evaluated against the second criteria Criteria2—(optional) the second criteria continuing up to Criteria_range127 and Criteria127) The formula in Figure EX 6.15 uses AVERAGEIFS to calculate the average delivery time for items currently on order that are out of stock. The values in the DeliveryTime range (the Delivery Time in Days column) are included in the range of values to be averaged only where the corresponding value in the ReorderStatus range (the On Order? column) match the criteria specified (yes) and the corresponding value in the InStock range (the Quantity in Stock column) match the criteria specified (0). =AVERAGEIFS(Sum_range,Criteria_range1,Criteria1,[Criteria_range2],[Criteria2]...) =AVERAGEIFS(DeliveryTime,ReorderStatus,"yes",InStock,0) To create a formula using AVERAGEIFS: Select the cell where you want to enter the formula. On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select AVERAGEIFS to open the Function Arguments dialog. In the Average_range argument box, enter the cell range or range name containing the values to be averaged if all the criteria are met. In the Criteria_range1 box, enter the cell range or range name containing the values to be evaluated against the first criteria. In the Criteria1 argument box, enter the text string, number, expression, or cell reference for the first criteria. Remember, text strings and expressions must be enclosed in quotation marks. In the Criteria_range2 box, enter the cell range or range name containing the values to be evaluated against the second criteria. In the Criteria2 argument box, enter the text string, number, expression, or cell reference for the second criteria. Remember, text strings and expressions must be enclosed in quotation marks. Continue entering the criteria range and criteria pairs until you are finished. Click OK.

Using CONCAT to Combine Text

To concatenate means to link items together. You can use the CONCAT function to combine the text values of cells or cell ranges. In Figure EX 3.13, the customer name in cell B3 is created by concatenating the values in column B (first name) and column C (last name). The formula looks like this: =CONCAT(B2 ," ", C2) The argument in the middle (" ") places a one‐space text string between the values of cells B2 and C2. If you are building a long string from multiple cells, you may want to use the Function Arguments dialog until you become familiar with this function. Of course, you can always type directly in the cell or formula bar and use Formula AutoComplete. On the Formulas tab, in the Function Library group, click the Text button. Click CONCAT. In the Function Arguments dialog, enter each cell reference or text string you want to combine in its own argument. If one of the arguments is a blank space, enter ;" " in the argument box. Click OK.

Using COUNTIF and COUNTIFS

You should be familiar with the basic counting functions COUNT, COUNTA, and COUNTBLANK that allow you to count the numbers, values, and blank cells in a cell range. Similar to SUMIF/SUMIFS and AVERAGEIF/AVERAGEIFS, there are also counting functions that allow you to count only data that meet specific criteria: COUNTIF and COUNTIFS. COUNTIF takes two arguments: Range—the range of cells to count Criteria—the conditions (the criteria) the cell must meet in order to be counted. The criteria can be a text string or numerical value or expression The formula in Figure EX 6.17 uses COUNTIF to count the number of cells where there are less than five items in stock. COUNTIF evaluates the values in the InStock range (the Quantity in Stock column). If the value matches the criteria specified (<5), then the cell is included in the count. Note that the expression criteria <5 must be enclosed in quotation marks. =COUNTIF(Range,Criteria) =COUNTIF(Instock,"<5") To create a formula using COUNTIF: Select the cell where you want to enter the formula. On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select COUNTIF to open the Function Arguments dialog. In the Range argument box, enter the cell range or named range to evaluate against the criteria. In the Criteria argument box, enter the criteria. The criteria can be a text string, numerical value, expression, or cell reference. Remember, text strings and expressions must be enclosed in quotation marks. Click OK. To count data that meet multiple criteria, use COUNTIFS. COUNTIFS takes arguments for up to 127 pairs of criteria ranges and criteria. The result of COUNTIFS is the average of all the cells that meet all criteria. COUNTIFS takes two required arguments and multiple optional arguments: Criteria_range1—the range of cells containing the values to be evaluated against the first criteria Criteria1—the first criteria Criteria_range2—(optional) the range of cells containing the values to be evaluated against the second criteria Criteria2—(optional) the second criteria (continuing up to Criteria_range127and Criteria127) The formula in Figure EX 6.19 uses COUNTIFS to count the number of rows with less than five items in stock and a delivery time of greater than seven days. COUNTIFS evaluates the values in the InStock range (the Quantity in Stock column) against the criteria <5 and the values in the DeliveryTime range (the Delivery Time in Days column) against the criteria >7. Where both criteria are met, the row is included in the count. Note that the criteria expressions <5 and >7 must be enclosed in quotation marks. =COUNTIFS(Criteria_range1,Criteria1,[Criteria_range2],[Criteria2]...) =COUNTIFS(InStock,"<5",DeliveryTime,">7") To create a formula using COUNTIFS: Select the cell where you want to enter the formula. On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select COUNTIFS to open the Function Arguments dialog. In the Criteria_range1 box, enter the cell range or range name containing the values to be evaluated against the first criteria. In the Criteria1 argument box, enter the text string, number, expression, or cell reference for the first criteria. In the Criteria_range2 box, enter the cell range or range name containing the values to be evaluated against the second criteria. In the Criteria2 argument box, enter the text string, number, expression, or cell reference for the second criteria. Continue entering the criteria range and criteria pairs until you are finished. Click OK.