Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 66 Sum or Counting Cells While Avoiding Error Values

Error values are useful warnings, but sometimes
you need to do calculations despite the errors. Choosing functions
that tolerate errors will let you do this.

When a range of cells contains one or more
error values, most formulas that reference that range of cells also
will return an error. You can overcome this frustration by using the
DSUM function.

Assume you
have a long list of numbers for which you need to get the sum total.
However, one of the cells, for whatever reason, is returning the #N/A
error.

Set up some data such as that shown in Figure 6-8.

Figure 6-8. Data set up to generate #N/A error message

To generate the #N/A error, enter the
formula =#N/A( ) in cells A2 and B2. Cell A12
uses a standard SUM function that sums cells
A2:A11, and because cell A2 has the #N/A error, the
SUM function also returns #N/A. The range D1:D2
has been named Criteria and is used as the last argument in the
DSUM function, which resides in
cell B12.

The syntax for the DSUM function (and all the
database functions) is as follows:

=DSUM(database,field,criteria)

The database
argument identifies the range of cells that comprise the list or
database. Within the database range, rows of related information are
treated as records, while columns of data are treated as fields. The
first row contains labels for all the columns.

The field
argument indicates which column is used in the function. The column
can be identified by name using the labels at the top of the column,
or it can be identified by position. The first column is 1, the
fourth column is 4, and so on.

The
criteria argument identifies a range of
cells containing conditions. The range used for the criteria must
include at least one column label plus at least one cell below the
column label that specifies a condition for the column.

If the data you want to sum will likely contain a variety of
different kinds of errors, you might need to consider using the
DSUM function with a wide range of criteria to
accommodate the possible errors. However, it is always best to
address the error at the source and eliminate it whenever possible
rather than work around it.

To
work around it, you again use the DSUM function,
but this time you need to set up criteria that span four columns,
expanding the named range criteria to include D1:G2 via Insert
Name Define, as shown in Figure 6-9.

Figure 6-9. The DSUM function used to ignore a number of different errors

Excel has a rich set of database
functions, and you can use any one of them in the same way. Consider
using the same method for DCOUNT,
DCOUNTA, DMAX,
DMIN, DPRODUCT,
etc.