COUNT and COUNTA Statistical Functions

Introduction:

In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Statistical functions apply a mathematical process to a group of cells in a worksheet. For example, the SUM function is used to add the values contained in a range of cells. The COUNT and COUNTA functions are also functions within the statistical grouping. The COUNT and COUNTA functions provide the number of cells that contain values in the provided cell range.

Learning:

The COUNT function is used to determine how many cells in a range contain a numeric entry. The COUNTA function is similar but is used to count the number of cell locations in a range that contain a text or numeric character. The syntax for the COUNT function is

=COUNT(value1, [value2], … )

The arguments for this function are the cells or cell range. When you intend to use a statistical function on a range of cells in a worksheet, make sure there are two cell locations separated by a colon and not a comma. If you enter two cell locations separated by a comma, the function will produce an output but it will be applied to only two cell locations instead of a range of cells.

An example below shows the result of the COUNT function. In the example below, cells B4 and B8 contain text and will not be counted using the COUNT function. The remaining values within the cell range of B1:B10 contain numbers and will be counted by the COUNT function.

There are times when you would want to count the number of textual values. For example, you might have a list of addresses, and you want to determine how many addresses there are. The COUNT function will not work, because the addresses will contain text. Excel has accounted for this with the COUNTA function. Notice the function name ends with “A” which signifies that alphanumeric (or textual) values will be included. This function counts the number of cells of any values: numbers, text, error. This function does not count any empty cells.

Using the same example, the COUNTA function will be include the values in B4 and B8. The result is shown below.

Summary:

The COUNT and COUNTA functions are used to return the count of cells within the function arguments. The COUNT function is used to determine how many cells in a range contain a numeric entry. The COUNTA function is similar but is used to count the number of cell locations in a range that contain a text or numeric character. The syntax for the COUNT function is =COUNT(value1, [value2], …) and for COUNTA is =COUNTA(value1, [value2], …) Cell ranges can be used for value1 or the optional values. When using cell ranges, the first and last cell within the range must be separated by a colon ( : ) and not a comma ( , )

Sources:

“2.3 Mathematical & Statistical Functions” by Emese Felvegi, Noreen Brown, Barbara Lave, Julie Romey, Mary Schatz, Diane Shingledecker, and Robert McCarn is licensed under CC BY 4.0