Chapter 7: Counting and Summing Techniques


image from book Download CD Content

Many of the most frequently asked spreadsheet questions involve counting and summing values and other worksheet elements. It seems that people are always looking for formulas to count or sum various items in a worksheet. If I've done my job, this chapter will answer the vast majority of such questions.

Counting and Summing Worksheet Cells

Generally, a counting formula returns the number of cells in a specified range that meet certain criteria. A summing formula returns the sum of the values of the cells in a range that meet certain criteria. The range you want counted or summed may or may not consist of a worksheet database or table.

Table 7-1 lists the worksheet functions that come into play when creating counting and summing formulas. If none of the functions in Table 7-1 can solve your problem, it's likely that an array formula can come to the rescue.

Table 7-1: EXCEL'S COUNTING AND SUMMING FUNCTIONS
Open table as spreadsheet

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 are new functions, available only in Excel 2007.

Cross Ref 

See Part IV for detailed information and examples of array formulas used for counting and summing. In addition, refer to Chapter 9 for information about summing and counting data in a list.

image from book
Getting a Quick Count or Sum

In Excel 97, Microsoft introduced the AutoCalculate feature. This feature displays, in the status bar, information about the selected range. By default, Excel 2007 displays the average, count, and sum of the selected cells. You can, however, right-click the status bar to bring up the Status Bar Configuration menu with some other options.

image from book

If you select Count, the status bar displays the number of nonempty cells in the selected range. If you select Numerical Count, the status bar displays the number of numeric cells in the selected range.

image from book




Excel 2007 Formulas
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
Year: 2007
Pages: 212

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net