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.

Looking For More?

Comments

Silicon Valley Accounting Solutions produces the Solution of the Week in order to share tips and shortcuts that promote process efficiency and highlight software features that may not be in your current toolbox. Please let us know if you have questions on how to accomplish a particular task or if you simply have a comment to share.

Even if you find that you know most of what you see here, it only takes one “Aha” to make regular visits worthwhile. To avoid missing any solutions, you may subscribe and have a new one delivered to your inbox each week.

Contact Us

We want to help you grow
your business, call us
at: (408) 260-5250 or
find out more here.

Controllership

When you need:
  • Clear processes for cash controls
  • Analysis of results
  • Customized reporting

Accounting

When you need:
  • Timely and accurate bookkeeping
  • Reconciliations and monthly closes
  • Familiarity with popular accounting software
Web Analytics