Calculating Number of Employees in Excel

HR metric – # of Employees

A very common metric in HR domain is # of Employees.

How many employees are active in our company?

Employees who have not started should not be counted as active. Employees who have given notice but still employed as of today should be counted. Essentially, only employees who are currently on payroll as of this moment should be counted..

In this article, I will explain how to calculate # of employees in a company using formulas in Excel. Formula we use will depend on how the input data is structured. I will take 4 common scenarios of input data and provide 4 simple Excel formulas to handle them.  I would love to hear from you if your company’s data is in one of these 4 types or if you have a different data structure. Please let me know in the comments.

Let’s get started.

Video Demo

Scenario 1: Only Active Employees in our dataset

This is the simplest scenario where you are already given only active employees in your dataset.

Let’s look at this table below.

Your dataset may have more columns, but for our calculations, these would suffice for now.

• ID is a unique number/alphanumeric that identifies an employee.
• Employee Name
• Start Date is the date when the employee started employment at the company.

As a general recommendation, please convert your input data to an Excel table by pressing Control+T when you have selected a cell in the data. Name the table.

Excel Tables

In order to calculate the # of employees,

Formula

In this case, we use the COUNTA function and choose the ID column in our T_EMP table (I had named the table T_EMP).

Scenario 2: Active and Future Employees in our dataset

If your dataset has employees who have not started yet, then our previous formula will result in inflating the count of employees. We need to only count when the employee’s start date is today or before today.

Formula

In this case, we use the COUNTIF function and choose the Start Date column in our T_EMP table (I had named the table T_EMP) with a condition <= TODAY.

This will ensure that only employees whose start date is less than or equal to today, will be counted.

Note: This will be dynamic and TODAY() function will always result in that day’s date.

Dynamic Formulas

Scenario 3: All Employees in our dataset with a Status column

Sometimes, you may receive a dataset where there is a Status column which will say ‘Active’ or ‘Inactive’ for each employee. All the employees regardless of when they started and if/when they left will be included.

In this case, we don’t have to worry about the Start Date. Since the data itself has a status column which takes care of determining which employee should be counted, we will use that instead.

Formula

In this case, we use the COUNTIF function and choose the Status column in our T_EMP table (I had named the table T_EMP) with a condition equal to “Active”.

In your dataset, instead of Active/Inactive, the values could be different. However, the concept of this formula will still work. Please replace ‘Active’ with the corresponding value in your data.

Scenario 4: Joiners and Leavers in 2 different datasets

Let’s make this a little bit more complicated. 😊

Assuming, we have the joiners (hires) and leavers (exits) in 2 separate datasets as shown below.

I have named the Joiners table T_EMP_JOINS and the leavers table T_EMP_EXITS.

we can use the following formula.

Formula

In this case, we use the COUNTIF function and count all the employees in the T_EMP_JOINS table with start date <= Today, and then subtract the leavers (count of employees in T_EMP_EXITS table with Exit date <= Today).

Conclusion

I hope this was useful. In your company, how do you calculate the number of employees? I would love to hear from you in the comments section. If there is a specific metric or KPI you would like me to do a video about, please let me know.

Read More

• Calculating Average Employee Tenure in Excel
• Calculating Gender Diversity
• Calculating Diversity Percentage