Part II: Working with Formulas and Functions
282
Note
If your data is in the form of a table, you can use autofiltering to accomplish many counting and summing
operations. Just set the autofilter criteria, and the table displays only the rows that match your criteria (the
nonqualifying rows in the table are hidden). Then you can select formulas to display counts or sums in the
table’s total row. See Chapter 5 for more information on using tables. n
TABLE 13.1Excel Counting and Summing Functions
Function Description
COUNT Returns the number of cells that contain a numeric value.
COUNTA Returns the number of nonblank cells.
COUNTBLANK Returns the number of blank cells.
COUNTIF Returns the number of cells that meet a specified criterion.
COUNTIFS* Returns the number of cells that meet multiple criteria.
DCOUNT Counts the number of records that meet specified criteria; used with a worksheet database.
DCOUNTA Counts the number of nonblank records that meet specified criteria; used with a worksheet
database.
DEVSQ Returns the sum of squares of deviations of data points from the sample mean; used pri-
marily in statistical formulas.
DSUM Returns the sum of a column of values that meet specified criteria; used with a worksheet
database.
FREQUENCY Calculates how often values occur within a range of values and returns a vertical array of
numbers. Used only in a multicell array formula.
SUBTOTAL When used with a first argument of 2, 3, 102, or 103, returns a count of cells that com-
prise a subtotal; when used with a first argument of 9 or 109, returns the sum of cells that
comprise a subtotal.
SUM Returns the sum of its arguments.
SUMIF Returns the sum of cells that meet a specified criterion.
SUMIFS* Returns the sum of cells that meet multiple criteria.
SUMPRODUCT Multiplies corresponding cells in two or more ranges and returns the sum of those products.
SUMSQ Returns the sum of the squares of its arguments; used primarily in statistical formulas.
SUMX2PY2 Returns the sum of the sum of squares of corresponding values in two ranges; used primar-
ily in statistical formulas.
SUMXMY2 Returns the sum of squares of the differences of corresponding values in two ranges; used
primarily in statistical formulas.
SUMX2MY2 Returns the sum of the differences of squares of corresponding values in two ranges; used
primarily in statistical formulas.
* These functions were introduced in Excel 2007.