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.”