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
Active Employees = (Joiners – Leavers)
Concept
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.
HR Templates and Tutorials