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.

Table 17-1 lists the Excel worksheet functions that come into play when creating counting and summing formulas. Not all these functions are covered in this chapter. If none of the functions in Table 17-1 can solve your problem, it’s likely that an array formula can come to the rescue.

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.

TABLE 17-1
Excel’s Counting and Summing Functions

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

• Another Excellent Email from TB on IRR I saw your last comment and wanted to provide you with an Excel sheet showing the implications of not counting...
• California Salmon Population Dropped 88% in 5 Years California Salmon are disappearing.  The number of King Salmon (aka the Chinook) returning to the Sacramento watershed was at a...
• Excel Tip: Have Your Cell Contents = Tab Names It's time for another edition of Stupid Excel Tricks! One of my coworkers recently asked me an Excel question.  He...
• Money Question Bonanza I'm not sure what sum counts as an official bonanza, but for the purposes of this article, I'm going to...
• Google Fusion Tables Could be a Game Changer Google has been very busy lately with many new products recently. Many of them, such as Google Public DNS, Google...