Counting Subtotal of Employees from Engineering Department in Excel

Purpose

This guide provides a step-by-step method to create a function in Excel that counts the subtotal of employees from the Engineering department using the SUBTOTAL and COUNTIF functions.

Function Overview

We will use a combination of Excel functions to achieve the desired result:

• FILTER: To filter the dataset based on the Engineering department.
• COUNTA: To count the non-empty cells in the filtered list or a defined range.

Example Dataset

Before using the function, ensure you have a dataset structured in a table format with the following columns:

• Employee Name
• Department

Excel Formula

Here's the formula to count employees from the Engineering department:

Formula

=COUNTA(FILTER(A2:A100, B2:B100="Engineering"))

Explanation

• FILTER(A2:A100, B2:B100="Engineering"): This function filters the range A2:A100 to include only those employees whose department (in range B2:B100) is "Engineering".
• COUNTA(...): This function counts the number of non-empty cells in the array returned by the FILTER function.

Handling Errors

To handle potential errors (like no matches found), you can use the IFERROR function:

Enhanced Formula

=IFERROR(COUNTA(FILTER(A2:A100, B2:B100="Engineering")), 0)

Explanation

• IFERROR(..., 0): This function checks if the result from COUNTA returns an error (e.g., when there are no employees from Engineering). If it does, it returns 0 instead.

Usage Example

Assuming your data is in the range A2:B100, use the formula in a different cell to get the total count of engineers:

1. In cell D1, enter the following formula:

   =IFERROR(COUNTA(FILTER(A2:A100, B2:B100="Engineering")), 0)

2. Press Enter.
3. Cell D1 now displays the count of employees in the Engineering department.

Conclusion

With these formulas, you can efficiently count the number of employees in the Engineering department in your dataset. This method is scalable for larger datasets, provided you adjust the range (A2:A100 and B2:B100) to match your actual data.

For further learning and advanced practices, consider exploring courses on the Enterprise DNA platform, which provides a wealth of resources for mastering Excel and data analysis.