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.
 
Search JabSto ::




Custom Search