Chapter 13: Creating Formulas That Count and Sum
283
Basic Counting Formulas ..................................................................................................
The basic counting formulas presented in this section are all straightforward and relatively simple.
They demonstrate the capability of the Excel counting functions to count the number of cells in a
range that meet specific criteria. Figure 13.1 shows a worksheet that uses formulas (in column E)
to summarize the contents of range A1:B10 — a 20-cell range named Data. This range contains a
variety of information, including values, text, logical values, errors, and empty cells.On the CD
This workbook is available on the companion CD-ROM. The file is named basic counting.xlsx.
Counting the total number of cells ..........................................................................
To get a count of the total number of cells in a range (empty and non-empty cells), use the follow-
ing formula. This formula returns the number of cells in a range named Data. It simply multiplies
the number of rows (returned by the ROWS function) by the number of columns (returned by the
COLUMNS function).=ROWS(Data)*COLUMNS(Data)This formula will not work if the Data range consists of noncontiguous cells. In other words, Data
must be a rectangular range of cells.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 to bring up a menu with other options. You can choose any or all the follow-
ing: Average, Count, Numerical Count, Minimum, Maximum, and Sum.Getting a Quick Count or Sum