How to Count Unique Employees by Department with Multiple Criteria in Excel
Question details
Calculate the distinct count of employees grouped by department using multiple criteria such as a specific date range and an employment status.
- Product
- Microsoft 365 or Excel for the web
- Device & OS
- not provided
- Scenario
- Generating a summarized report that counts the unique number of active or inactive employees within each department, filtered by start and end dates.
- Observed behavior
- Dynamic array functions like FILTER, UNIQUE, LET, CHOOSECOLS, and VSTACK can be combined to automatically evaluate multiple conditions and return an aggregated distinct count per department.
Ensure you are using a version of Excel that supports dynamic array formulas, such as Microsoft 365 or Excel 2021, and verify that your dataset contains no blank rows that could break table references.
Use a Dynamic Array Formula with Structured Table References
This method uses the LET, FILTER, and UNIQUE functions to efficiently count distinct names grouped by department based on date and status criteria.
By converting your data into an Excel Table, the formula can automatically adapt to new rows. The LET function is used to define variables, making the long array calculation more readable and performant.
Select your dataset and press Ctrl + T to convert it into a Table. Name the table 'Table1'. Ensure your columns are labeled correctly, for example: Name, Dept, Date, and Status.
Designate specific cells for your filter conditions. For instance, use cell I4 for the Start Date, I5 for the End Date, and I6 for the Employee Status (e.g., 'Active').
Select the top-left cell where you want the summary table to appear. Enter the following formula: =LET(_headers,{"Dept","Count"},IF(COUNTA(I4:I6)<3,_headers,LET(pre_filter,FILTER(Table1[[Name]:[Dept]],(Table1[Date]>=I4)*(Table1[Date]<=I5)*(Table1[Status]=I6),NA()),col_dept,CHOOSECOLS(pre_filter,2),_depts,SORT(UNIQUE(Table1[Dept])),CntRws,LAMBDA(dept,ROWS(UNIQUE(FILTER(pre_filter,col_dept=dept,NA())))),VSTACK(_headers,HSTACK(_depts,IFNA(BYROW(_depts,CntRws),0))))))
Press Enter. The formula will spill vertically and horizontally to generate a complete summary table containing the department names and their respective distinct employee counts.
Alternative Formula for Standard Cell Ranges
Use this approach if your data isn't formatted as an Excel Table and you need to rely on standard absolute ranges.
Efficiently Analyze Complex Data with WPS Spreadsheet
WPS Spreadsheet provides powerful data processing capabilities, fully compatible with advanced array structures, making it incredibly easy to filter datasets and count distinct values.
- 1. Open Your Dataset: Launch WPS Spreadsheet and open your existing .xlsx file containing the employee data.
- 2. Apply Formulas or PivotTables: Utilize built-in advanced PivotTable functionalities or apply nested array formulas to quickly generate distinct counts by department.
- 3. Save and Export: Once your analysis is complete, seamlessly save the results in Excel format or export directly to PDF.
Frequently Asked Questions
Can I use COUNTIFS to count unique values in Excel?
No, COUNTIFS alone counts total occurrences and will count duplicate employee names multiple times. To count distinct values, you must combine it with functions like SUM and 1/COUNTIFS, or preferably use the modern UNIQUE function available in newer Excel versions.
Why does my array formula return a #SPILL! error?
A #SPILL! error occurs when the dynamic array formula does not have enough empty adjacent cells to display the output. Clear any text, spaces, or data in the blocking cells to allow the formula to fully spill the unique results.
Do these unique count formulas work in Excel 2016?
No. Functions such as UNIQUE, FILTER, LET, and BYROW are dynamic array functions that require Microsoft 365, Excel 2021, or Excel for the web. For older versions, you must use complex legacy array formulas combining INDEX, MATCH, and FREQUENCY entered with Ctrl+Shift+Enter.
