Formula Excel : Counting Values in a Range

Excel provides several functions to count the values in a range: COUNT, COUNTA, and COUNTBLANK. Each of these functions provides a different method of counting based on whether the values in your range are numbers, numbers and text, or blank.

Image 2-13 illustrates the different kinds of counting you can perform. In row 12, the COUNT function is counting only exams where students have passed. In column H, the COUNTA function is counting all the exams taken by a student. In column I, the COUNTBLANK function is counting only those exams that have not yet been taken.

Image 2-13: A demonstration of counting cells.

How it works

The COUNT function counts only numeric values in a given range. It requires only a single argument in which you pass a range of cells. For example, this formula counts only those cells in range C4:C8 that contain a numeric value:

=COUNT(C4:C8)

The COUNTA function counts any cell that is not blank. You can use this function when you’re counting cells that contain any combination of numbers and text. It requires only a single argument in which you pass a range of cells. For instance, this formula counts all the nonblank cells in range C4:F4:

=COUNTA(C4:F4)

The COUNTBLANK function counts only the blank cells in a given range. It requires only a single argument in which you pass a range of cells. For instance, this formula counts all the blank cells in range C4:F4:

=COUNTBLANK(C4:F4)

Categories: Formula