Microsoft Office Tutorials and References
In Depth Information
Summarizing data that meets specific conditions
Now you need to have Excel display messages that indicate whether Craig Dewar should
evaluate the account for a possible rate adjustment. To have Excel print a message from an
IF function, you enclose the message in quotes in the Value_if_true or Value_if_false box.
In this case, you would enter “High-volume shipper—evaluate for rate decrease”
(including the quotation marks) in the Value_if_true box and “Does not qualify at this time.” in the
Value_if_false box.
Excel also includes several other conditional functions you can use to summarize your data,
as shown in the following table.
3
Function
Description
AVERAGEIF
Finds the average of values within a cell range that meet a specified criterion
AVERAGEIFS
Finds the average of values within a cell range that meet multiple criteria
COUNT
Counts the number of cells in a range that contain a numerical value
COUNTA
Counts the number of cells in a range that are not empty
COUNTBLANK
Counts the number of cells in a range that are empty
COUNTIF
Counts the number of cells in a range that meet a specified criterion
COUNTIFS
Counts the number of cells in a range that meet multiple criteria
IFERROR
Displays one value if a formula results in an error and another if it doesn’t
SUMIF
Finds the sum of values in a range that meet a single criterion
SUMIFS
Finds the sum of values in a range that meet multiple criteria
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 when looking up the CustomerID value from cell G8 in the
Customers table by using the VLOOKUP function. One way to create such a formula is by
using =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 it doesn’t
find a match, it displays the text Customer not found .
SEE ALSO For more information about the VLOOKUP function, see “Looking up information
in a worksheet” in Chapter 6, “Reordering and summarizing data.”
Search JabSto ::




Custom Search