Microsoft Office Tutorials and References

In Depth Information

**Chapter 17: Creating Formulas That Count and Sum**

TABLE 17.1
Excel 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 speciﬁ ed criterion.

COUNTIFS
*

Returns the number of cells that meet multiple criteria.

DCOUNT

Counts the number of records that meet speciﬁ ed criteria; used with a

worksheet database.

DCOUNTA

Counts the number of nonblank records that meet speciﬁ ed criteria; used

with a worksheet database.

DSUM

Returns the sum of a column of values that meet speciﬁ ed 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 ﬁ rst argument of
2
,
3
,
102
, or
103
, returns a
count
of cells

that comprise a subtotal; when used with a ﬁ rst 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 speciﬁ ed 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.

* These functions were introduced in Excel 2007.

Getting a Quick Count or Sum

The Excel status bar can display useful information about the currently selected cells — no formulas

required. Normally, the status bar displays the sum and count of the values in the selected range. You

can, however, right-click the status bar to bring up a menu with other options. You can choose any or

all of the following: Average, Count, Numerical Count, Minimum, Maximum, and Sum.