Summarize data that meets specific conditions
Formulas can also be used to display messages when specific conditions are met. These are known as conditional formulas. One common method to create a conditional formula in Excel is by using the IF function. You can access the IF function by clicking the Insert Function button next to the formula bar, then selecting the IF function. This opens the Function Arguments dialog box, which contains the fields needed to construct an IF formula.
The Function Arguments dialog box for the IF function includes three fields: Logical_test, Value_if_true, and Value_if_false. The Logical_test field specifies the condition to evaluate.
To display messages based on whether the condition is met, you can enter the desired text in quotes in the Value_if_true or Value_if_false fields. For example, you might type "High-values" in the Value_if_true field and "Don't buy it" in the Value_if_false field.
In addition to the IF function, Excel provides several other conditional functions that can be used to summarize data, as outlined in the following table.
The following table describes some of the most useful functions in the list.
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 use an IFERROR formula in a table named Customers, based on the customer identification number entered into cell A2. That formula might look like this: =IFERROR(A2,”Customer not found”). If the function finds a match for the CustomerID in cell A2, it displays the customer’s name; if it doesn’t displays the text Customer not found.
Summarize data by using the IF function
- Click the cell in which you want to enter the formula.
- 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.
Create a formula by using the Insert Function dialog box
- To the left of the formula bar, click the Insert Function button.
- In the Insert Function dialog box, click the function you want to use in your formula.
- Click Next.
- In the Function Arguments dialog box, define the arguments for the function you chose.
- Click Insert.
Count cells that contain numbers in a range
- Click the cell in which you want to enter the formula.
- Create a formula with the syntax =COUNT(range), where range is the cell range in which you want to count cells.
Count cells that are non-blank
- Click the cell in which you want to enter the formula.
- Create a formula with the syntax =COUNTA(range), where range is the cell range in which you want to count cells.
Count cells that contain a blank value
- Click the cell in which you want to enter the formula.
- Create a formula with the syntax =COUNTBLANK(range), where range is the cell range in which you want to count cells.
Count cells that meet one condition
- Click the cell in which you want to enter the formula.
- 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 or not.
Count cells that meet multiple conditions
- Click the cell in which you want to enter the formula.
- 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 or not.
Find the sum of data that meets one condition
- Click the cell in which you want to enter the formula.
- 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 or not.
- sum_range is the range that contains the values to be included if the range cell in the same row meets the criterion.
Find the sum of data that meets multiple conditions
- Click the cell in which you want to enter the formula.
- 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 or not.
Find the average of data that meets one condition
- Click the cell in which you want to enter the formula.
- 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 or not.
- average_range is the range that contains the values to be included if the range cell in the same row meets the criterion.
Find the average of data that meets multiple conditions
- Click the cell in which you want to enter the formula.
- 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 or not.
Display a custom message if a cell contains an error
- Click the cell in which you want to enter the formula.
- 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.
Feedback
Was this page helpful?