Excel: COUNT Function
When using specialized functions in Excel, it is important to understand their underlying logic in order to be able to rely on the results. Here is an explanation of the various COUNT functions.
In this Employee forecast worksheet, a monthly headcount is required.
Using the basic COUNT function, Excel shows Headcount growing from 4 to 11 over the course of the year. However, a closer look at the data itself indicates only 3 employees are on board in January. This difference occurs because the COUNT function looks for non-blank cells that have a numeric value. Therefore it counted cell C8 because the value was numeric even though it is zero.
It is not uncommon to replace numeric cells with zeroes when updating worksheets to adjust the timing of events. It can however produce the inconsistency found on row 22. An easy fix while updating, is to simply ‘clear’ the contents of these cells making them blank instead of zero.
A safer remedy to avoid this error in all future updates, however, is to use the COUNTIF function and stipulate cells must be greater than zero to be counted. You can see now that the headcount range for the year is 3 to 11 and shows the number of ‘active’ employee correctly for January through July.
There is also a COUNTIFS function that allows multiple conditions for cells to qualify to be counted. In this example, we can calculate how many of the employees are ‘part-time’ by adding a second qualifying statement. We have assumed that if Monthly pay is < $5k, then the employee is considered ‘part-time’.
It’s important to test formula results in your worksheets to avoid errors.