Perform calculations on data

Summarize data that meets specific conditions

Another use for formulas is to display messages when certain conditions are met. This kind of formula is called a conditional formula. One way to create a conditional formula in Excel is to use the IF function. Clicking the Insert Function button next to the formula bar and then choosing the IF function displays the Function Arguments dialog box with the fields required to create an IF formula.

The Function Arguments dialog box for an IF formula.

When you work with an IF function, the Function Arguments dialog box has three boxes:

• Logical_test This box holds the condition you want to check.

• Value_if_true This box holds the message that will be displayed if the condition is met, enclosed in quotes. For example, in this case, you could type “High-volume shipper—evaluate for rate decrease.”

• Value_if_false This box holds the message that will be displayed if the condition is not met, enclosed in quotes. For example, in this case, you could type “Does not qualify at this time.”

Excel also includes several other conditional functions you can use to summarize your data, as shown in the following table.

As noted, the COUNTIF function counts the number of cells that meet a criterion, the SUMIF function finds the total of values in cells that meet a criterion, and the AVERAGEIF function finds the average of values in cells that meet a criterion. To create a formula that uses the AVERAGEIF function, you define the range to be examined for the criterion, the criterion, and, if required, the range from which to draw the values. As an example, consider a worksheet that lists each customer’s ID number, name, state, and total monthly shipping bill. If you want to find the average order of customers from the state of Washington (abbreviated in the worksheet as WA), you can create the formula =AVERAGEIF(C3:C6, “WA”, D3:D6).

A list of data that contains customer information.

The AVERAGEIFS, SUMIFS, and COUNTIFS functions extend the capabilities of the AVERAGEIF, SUMIF, and COUNTIF functions to allow for multiple criteria. For example, if you want to find the sum of all orders of at least $100,000 placed by companies in Washington, you can create the formula =SUMIFS(D3:D6, C3:C6, “=WA”, D3:D6, “>=100000”).

The AVERAGEIFS and SUMIFS functions start with a data range that contains values that the formula summarizes. You then list the data ranges and the criteria to apply to that range. In generic terms, the syntax is =AVERAGEIFS(data_range, criteria_range1, criteria1[,criteria_range2, criteria2...]). The part of the syntax in brackets (which aren’t used when you create the formula) is optional, so an AVERAGEIFS or SUMIFS formula that contains a single criterion will work. The COUNTIFS function, which doesn’t perform any calculations, doesn’t need a data range; you just provide the criteria ranges and criteria. For example, you could find the number of customers from Washington who were billed at least $100,000 by using the formula =COUNTIFS(D3:D6, “=WA”, E3:E6, “>=100000”).

You can use the IFERROR function to display a custom error message instead of relying on the default Excel error messages to explain what happened. For example, you could create this type of formula to employ the VLOOKUP function to look up a customer’s name in the second column of a table named Customers based on the customer identification number entered into cell G8. That formula might look like this: =IFERROR(VLOOKUP(G8,Customers,2,FALSE),”Customer not found”). If the function finds a match for the CustomerID in cell G8, it displays the customer’s name; if not, it displays the text Customer not found.

To summarize data by using the IF function

1. Click the cell in which you want to enter the formula.

2. Enter a formula with the syntax =IF(Logical_test, Value_if_true, Value_if_false) where:

   • Logical_test is the logical test to be performed.

   • Value_if_true is the value the formula returns if the test is true.

   • Value_if_false is the value the formula returns if the test is false.

To create a formula by using the Insert Function dialog box

1. To the left of the formula bar, click the Insert Function button.

2. In the Insert Function dialog box, click the function you want to use in your formula.

3. Click OK.

4. In the Function Arguments dialog box, define the arguments for the function you chose.

5. Click OK.

To count cells that contain numbers in a range

1. Click the cell in which you want to enter the formula.

2. Create a formula with the syntax =COUNT(range), where range is the cell range in which you want to count cells.

To count cells that are non-blank

1. Click the cell in which you want to enter the formula.

2. Create a formula with the syntax =COUNTA(range), where range is the cell range in which you want to count cells.

To count cells that contain a blank value

1. Click the cell in which you want to enter the formula.

2. Create a formula with the syntax =COUNTBLANK(range), where range is the cell range in which you want to count cells.

To count cells that meet one condition

1. Click the cell in which you want to enter the formula.

2. Enter a formula of the form =COUNTIF(range, criteria) where:

   • range is the cell range that might contain the criteria value.

   • criteria is the logical test used to determine whether to count the cell.

To count cells that meet multiple conditions

1. Click the cell in which you want to enter the formula.

2. Enter a formula of the form =COUNTIFS(criteria_range, criteria,…) where for each criteria_range and criteria pair:

   • criteria_range is the cell range that might contain the criteria value.

   • criteria is the logical test used to determine whether to count the cell.

To find the sum of data that meets one condition

1. Click the cell in which you want to enter the formula.

2. Enter a formula of the form =SUMIF(range, criteria, sum_range) where:

   • range is the cell range that might contain the criteria value.

   • criteria is the logical test used to determine whether to include the cell.

   • sum_range is the range that contains the values to be included if the range cell in the same row meets the criterion.

To find the sum of data that meets multiple conditions

1. Click the cell in which you want to enter the formula.

2. Enter a formula of the form =SUMIFS(sum_range, criteria_range, criteria,…) where:

   • sum_range is the range that contains the values to be included if all criteria_range cells in the same row meet all criteria.

   • criteria_range is the cell range that might contain the criteria value.

   • criteria is the logical test used to determine whether to include the cell.

To find the average of data that meets one condition

1. Click the cell in which you want to enter the formula.

2. Enter a formula of the form =AVERAGEIF(range, criteria, average_range) where:

   • range is the cell range that might contain the criteria value.

   • criteria is the logical test used to determine whether to include the cell.

   • average_range is the range that contains the values to be included if the range cell in the same row meets the criterion.

To find the average of data that meets multiple conditions

1. Click the cell in which you want to enter the formula.

2. Enter a formula of the form =AVERAGEIFS(average_range, criteria_range, criteria,…) where:

   • average_range is the range that contains the values to be included if all criteria_range cells in the same row meet all criteria.

   • criteria_range is the cell range that might contain the criteria value.

   • criteria is the logical test used to determine whether to include the cell.

To display a custom message if a cell contains an error

1. Click the cell in which you want to enter the formula.

2. Enter a formula with the syntax =IFERROR(value, value_if_error) where:

   • value is a cell reference or formula.

   • value_if_error is the value to be displayed if the value argument returns an error.