Understanding Excel’s COUNT Function Variations

Excel contains a wide range of functions which are conveniently arranged into categories. The COUNT functions are found in the statistical category. There are five of them; COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS. We will examine the COUNTIF and COUNTIFS functions in another article. In this article, we will be looking at COUNT, COUNTA and COUNTBLANK.

The COUNT function returns the number of cells within a given range or series of ranges that contain numbers. COUNTA simply counts the cells which aren’t blank. COUNTBLANK does the reverse: it counts the cells in a range which are black.

For the purposes of illustration, let us say that we have a spreadsheet containing the scores achieved by students in a range of subjects. For each student, we can input any one of three entries under each subject heading: firstly, we can input a number representing the score achieved; secondly, we can input the letter “X”, indicating a subject the student was scheduled to take but missed; and, thirdly, we can leave the cell blank, indicating a subject that they were not scheduled to take.

If we want to count the number of exams each student actually took, we would use the COUNT function. If we want to count the number of exams each was scheduled to take, we would use COUNTA. Finally, if we would like to know the number of exams they were not scheduled to take, we would use COUNTBLANK.

To build the formula for calculating the number of exams taken, we can click in the appropriate call and then click the Insert Function button on the left of the formula bar. Next, we would highlight the statistical category and scroll down to “COUNT”. As always Excel reminds us of what this function does: it “counts the number of cells in a range that contain numbers”.

When we click OK, the Function Wizard appears and we are prompted to specify the parameters required by this function. In almost all circumstances, these parameters will consist of cell references. We can simply drag across the cells that we want to count and Excel will create the reference for us. We can then click OK and Excel Will return the correct value.

Next, we would repeat the same procedure to create a formula containing the COUNTA function and another for containing COUNTBLANK. Finally, we would copy the formula down by highlighting the cells containing the original formulas and dragging the AutoFill handle down.

The The writer of this article is a trainer and developer with TrainingCompany.Com, an independent computer training company offering Microsoft Excel 2007Classes at their central London training centre.