How to Use Statistical Functions in Excel to Count Items in a Data Set

By Stephen L. Nelson, E. C. Nelson

Excel provides four useful statistical functions for counting cells within a worksheet or list: COUNT, COUNTA, COUNTBLANK, and COUNTIF. Excel also provides two useful functions for counting permutations and combinations: PERMUT and COMBIN.

COUNT: Counting cells with values

The COUNT function counts the number of cells within a specified range that hold values. The function, however, doesn’t count cells containing the logical values TRUE or FALSE or cells that are empty. The function uses the syntax

=COUNT(value1,[value2])

If you want to use the COUNT function to count the number of values in the range B2:B10 in this worksheet, you might enter the formula

=COUNT(B2:B10)

into cell G2. The function returns the value 9.

image0.jpg

You can include several arguments as part of the range argument in the COUNT function. For example, you might also use the syntax =COUNT(B2,B3:B5,B6:B7,B8,B9), which would return the same result as the formula you see here.

COUNTA: Alternative counting cells with values

The COUNTA function counts the number of cells within a specified range that aren’t empty. The function uses the syntax

=COUNTA(value1,[value2])

If you want to use the COUNTA function to count the number of non-empty cells in the range A1:B2 in the worksheet, for example, enter the formula

=COUNTA(A1:B2)

into cell G4. The function returns the value 3.

COUNTBLANK: Counting empty cells

The COUNTBLANK function counts the number of cells within a specified range that are empty. The function uses the syntax

=COUNTBLANK(value1,[value2])

To use the COUNTBLANK function to count the number of empty cells in the range A1:B2 in the worksheet, for example, you could enter the formula

=COUNTBLANK(A1:B2)

into cell G6. The function returns the value 1.

COUNTIF: Counting cells that match criteria

The COUNTIF function counts the number of cells within a specified range that match criteria that you specify. The function uses the syntax

=COUNTIF(range,criteria)

where range is the worksheet range in which you count cells and criteria is a Boolean expression, enclosed in quotation marks, that describes your criteria.

As an example of how this works, suppose you want to use the COUNTIF function to count the number of cells within the worksheet range C1:C10 that hold values greater than 4. To make this count, you use the following formula:

=COUNTIF(C1:C10,">4")

This formula appears in cell G8 of the worksheet.

You can use other Boolean operators to construct other match criteria: Use the < operator for a less-than comparison, the <= operator for a less-than-or-equal-to comparison, the >= operator for a greater-than-or-equal-to comparison, the = operator for the equal-to comparison, and the <> operator for a not-equal-to comparison.

PERMUT: Counting permutations

The PERMUT function counts the number of permutations possible when selecting a sample from a population. Note that for a permutation, the order does matter in which items are selected. The function uses the syntax

=PERMUT(number,number_chosen)

where number is the number of items in the population and number_chosen is the number of items selected. Given a population of six items and three selections, for example, you calculate the number of permutations by using the formula

=PERMUT(6,3)

The function returns the value 120, indicating that 120 different ways exist in which three items can be selected from a set of six.

COMBIN: Counting combinations

If the order in which items are selected doesn’t matter, you use the combination function, COMBIN, which uses the syntax

=COMBIN(number,number_chosen)

The number of combinations possible when three items are selected from a set of six can be calculated using the formula

=COMBIN(6,3)

This function returns the value 20. The COMBIN function isn’t technically an Excel statistical function, by the way, but is closely related.