Microsoft Office Tutorials and References

In Depth Information

**Chapter 7: Counting and Summing Techniques**

See Part IV for detailed information and examples of array formulas that you can use

for counting and summing. In addition, refer to Chapter 9 for information about

summing and counting data in a list.

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. Refer to Chapter 9 for more information on using tables.

Table 7-1:
Excel’s Counting and Summing Functions

Function
Description

COUNT Returns the number of cells in a range that contain a numeric value

COUNTA Returns the number of nonblank cells in a range

COUNTBLANK Returns the number of blank cells in a range

COUNTIF Returns the number of cells in a range that meet a single specified criterion

COUNTIFS* Returns the number of cells in a range that meet one or more specified criterion

DCOUNT Counts the number of records in a worksheet database that meet specified criteria

DCOUNTA Counts the number of nonblank records in a worksheet database that meet specified

criteria

DEVSQ Returns the sum of squares of deviations of data points from the sample mean; used

primarily in statistical formulas

DSUM Returns the sum of a column of values in a worksheet database that meet specified criteria

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 or 3, returns a count of cells that comprise a

subtotal; when used with a first argument of 9, returns the sum of cells that comprise a subtotal

SUM Returns the sum of its arguments

SUMIF Returns the sum of cells in a range that meet a specified criterion

SUMIFS* Returns the sum of the cells in a range that meet one or more specified criterion

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

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