Function Problems

How to Count Unique Organization IDs and Employee Records in Excel

WPS Editor Oct 12, 2026 868 views

Question details

The user needs to extract unique organization IDs from a dataset and count the total number of employee records associated with each ID, ensuring the final counts sum up to the total record count.

How to Count Unique Organization IDs and Employee Records in Excel

Product: Excel
Device & OS: not provided
Scenario: Summarizing a large dataset by extracting distinct categories (e.g., organization IDs) and calculating the frequency of entries for each category.
Observed behavior: A unique list of IDs is generated, and a count per organization is calculated, with the grand total exactly matching the number of original records.

Before you start

Ensure your dataset does not contain empty rows within the target range, and verify that your column headers are clearly labeled before applying formulas or inserting PivotTables.

Solution 1Recommended

Use UNIQUE and COUNTIF Functions

Extract unique values and count their specific occurrences using a combination of dynamic array and statistical functions.

This method uses Excel's modern dynamic array formula to fetch distinct items, and then counts how many times each item appears. Note that the UNIQUE function requires newer versions of Excel or WPS Office.

Extract Unique IDs

Select a blank cell next to your data (e.g., C2) and enter the formula =UNIQUE(A2:A1501), assuming your organization IDs are located in column A. Press Enter to dynamically spill a unique list of IDs.

Count Records Per ID

In the adjacent cell (e.g., D2), type the formula =COUNTIF($A$2:$A$1501, C2) to calculate how many times that specific unique ID appears in the original dataset.

Apply Formula to All Unique IDs

Select cell D2, click and hold the fill handle (the small square at the bottom right corner of the cell), and drag it down to apply the count calculation to all unique IDs in your list.

Verify the Total Count

At the bottom of your new counts column, use the sum formula (e.g., =SUM(D2:D50)) to ensure the total matches your source record count, such as 1,500 employees.

Dynamic Updates: As long as you format your source data as a Table or adjust your formula ranges, the UNIQUE function will automatically update if new organization IDs are added to the list.

Solution 2

Create a PivotTable for Automatic Grouping and Counting

A PivotTable is a powerful tool to quickly group unique IDs and summarize their counts without writing any formulas.

Efficient Data Analysis Tool

Count Unique Values Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like UNIQUE and COUNTIF, alongside robust PivotTable features, empowering you to clean and summarize large datasets efficiently without complex workarounds.

1. Open Data: Launch WPS Spreadsheet and open your dataset containing the organization records.
2. Extract Distinct Values: Use the =UNIQUE(range) formula in an empty column to instantly pull out distinct organization IDs.
3. Calculate Totals: Type =COUNTIF(range, criteria) in the next cell to count the number of corresponding records for each organization.
4. Use PivotTables: Alternatively, click 'Insert' > 'PivotTable' to categorize and tally records automatically.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Supports dynamic array functions like UNIQUE and FILTER.Intuitive PivotTable interface for instant data summarization.Lightweight, fast, and free to download for everyday data processing.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the UNIQUE function returning a #NAME? error?

The UNIQUE function is a dynamic array formula available in newer versions of Excel (Microsoft 365) and WPS Office. If you see a #NAME? error, your current software version may not support this function. In this case, use the PivotTable method instead to get the same results.

How can I automatically sort my unique list of organization IDs?

You can wrap the SORT function around the UNIQUE formula. Simply edit your formula to =SORT(UNIQUE(A2:A1501)). This will extract the distinct IDs and automatically arrange them in alphabetical or ascending numerical order.

Why doesn't the total count match my original number of records?

This usually happens if there are blank cells, hidden trailing spaces, or slight spelling variations in your source data. For example, 'Org A' and 'Org A ' (with a space) will be counted as two different entities. You can use the TRIM function to remove extra spaces from your source data before counting.

Excel & Data Problems