| Microsoft Excel 2007 Functions: COUNT, COUNTA And COUNTBLANK |
View Comments
| Computers - Computers |
| Written by Andrew Whiteman |
| Thursday, 12 March 2009 09:02 |
|
Excel contains a huge range of functions which are conveniently arranged into categories. The COUNT function is found in the statistical category. There are five variations; COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS. We will examine COUNTIF and COUNTIFS in another article. In this article, we will look at COUNT, COUNTA and COUNTBLANK.
Excel contains a huge range of functions which are conveniently arranged into categories. The COUNT function is found in the statistical category. There are five variations; COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS. We will examine COUNTIF and COUNTIFS in another article. In this article, we will look at COUNT, COUNTA and COUNTBLANK. The COUNT function itself returns the number of cells within a given range or series of ranges that contain numbers. The COUNTA function counts all those cells which aren't blank. COUNTBLANK does the exact opposite: it counts the cells in a range which are black. For the purposes of illustration, let's say that we have a spreadsheet containing the scores achieved by students in a variety of subjects. For each student, we can input one of three entries under each subject: firstly, a number representing the score achieved; secondly, the letter "X" indicating a subject the student was scheduled to take but missed; and, thirdly, the cell can be left 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 create the formula for calculating the number of exams taken, we would click in the appropriate call and click the Insert Function button on the left of the formula bar. Next, we highlight the statistical category and scroll down to "COUNT". As always Excel reminds us of what this function will do: it "counts the number of cells in a range that contain numbers". When we click OK, the Function Wizard pops up and we are prompted to specify the parameters required by this function. It is almost always the case that these parameters consist of cell references. We can simply drag across the cells that we want to count and Excel will generate our reference for us. We can then click the OK button and Excel Will return the correct value. Next, we would repeat this same procedure to create a formula containing the COUNTA function and another for containing COUNTBLANK. Finally, we would copy the formula down: simply highlight the cells containing the original formulas and drag the AutoFill handle down. About the Author: Author is a developer and trainer with Macresource Computer Solutions, a UK IT training company offering Microsoft Excel 2007 training courses at their central London training centre. |