Function Problems

How to Count Unique Employees by Latest Status in WPS Spreadsheet

Rana Garcia Sep 25, 2026 869 views

Question details

The user needs to count employee records only once based on their most recent employment status or termination date, avoiding duplicates caused by multiple rehire or termination events.

How to Count Unique Employees Using Their Latest Employment Status

Product: Spreadsheet
Device & OS: not provided
Scenario: Calculating accurate headcount or termination totals from HR data where employees have multiple historical status records (e.g., original hire, termination, and rehire dates).
Observed behavior: Basic count formulas evaluate all rows, resulting in duplicate counts for employees with multiple records (e.g., returning 27 instead of the correct 21).

Before you start

Ensure your dataset is organized into clear columns (e.g., Employee ID, Status, and Date) and that you are using a spreadsheet version that supports modern dynamic array functions like UNIQUE and FILTER.

Solution 1Recommended

Use a Dynamic Array Formula to Filter and Count

Combine advanced array functions to automatically extract unique employee IDs and count them based on their most recent status.

By utilizing functions like LET, UNIQUE, and FILTER, you can isolate the latest record for each employee. This approach prevents the formula from double-counting staff members who have been terminated and subsequently rehired.

Identify your data ranges

Determine the columns containing your Employee IDs or Names (e.g., B2:B20) and the corresponding termination or status dates (e.g., D2:D20).

Apply the array formula

Select an empty cell and enter the formula: =LET(n,B2:B20,td,D2:D20,COUNTA(BYROW(UNIQUE(TOCOL(n,3)),LAMBDA(a,TEXTJOIN(",",,FILTER(td,n=a)))))). Adjust the ranges (B2:B20 and D2:D20) to match your actual dataset.

Verify the calculation

Press Enter. The formula will evaluate the array, returning a count of unique employees mapped to their most recent applicable status date.

Use a Dynamic Array Formula to Filter and Count

Formula Customization: If you only want to count a specific status (such as 'Terminated'), you can add an additional condition inside the FILTER function to match that specific text criteria.

Solution 2

Use a Pivot Table to Deduplicate Data

If you prefer a visual interface over complex formulas, Pivot Tables can group employees and extract their latest status date effortlessly.

Easily Manage Complex HR Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas like UNIQUE, FILTER, and LET, making it simple to process and count complex employee records without manual deduplication.

1. Open your HR data file: Launch WPS Spreadsheet and open your employee dataset containing the IDs, statuses, and dates.
2. Select a destination cell: Click on the cell where you want the final unique employee count to be displayed.
3. Input the array formula: Navigate to the formula bar and input your combination of UNIQUE and FILTER functions to isolate the latest statuses.
4. Calculate instantly: Press Enter to instantly process the data and display the accurate, deduplicated headcount.

Fully compatible with Microsoft Excel file formats (.xlsx)Supports modern dynamic array functions for advanced data analysisLightweight and fast, even when handling large HR datasetsIntuitive Pivot Table features for quick deduplication

Frequently Asked Questions

Why is my standard COUNTA function returning duplicate employee records?

The COUNTA function simply counts all non-empty cells in a given range. If an employee has multiple rows in your dataset (e.g., an original hire date and a separate rehire date), COUNTA will count every single instance. You must nest it with functions like UNIQUE to filter out the duplicates.

Can I count only active employees using these formulas?

Yes. You can add a specific condition inside the FILTER function to only include rows where the status column equals 'Active'. The UNIQUE function will then process only the filtered active records, ensuring an accurate headcount.

What does the LET function do in this formula?

The LET function allows you to assign names to ranges or calculation results within a formula (for example, naming the range B2:B20 as 'n'). This makes long, complex formulas easier to read, write, and calculate, as the system only needs to evaluate the named range once.

Excel & Data Problems