How to Count Repeated Names or Employee Numbers in Excel
Question details
The user needs to count the number of times a specific name or employee number appears in an Excel column.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing employee data or identifying duplicate entries.
- Observed behavior
- To obtain the exact count of specific names or IDs, or to generate a complete summary report of all repeated entries.
Ensure your data column does not have leading or trailing spaces, as these can affect the accuracy of functions and PivotTables when counting duplicates.
Use the COUNTIF Function to Count Specific Names or Numbers
The COUNTIF function is perfect for finding exactly how many times a specific name or employee number appears in a designated range.
The COUNTIF function searches through a specified range and returns the number of cells that match your exact criteria. This method is highly effective when you want a quick answer for a single employee or a specific subset of names without altering your data layout.
Click on an empty cell where you want the counted result to appear.
Type `=COUNTIF(A:A, 34678)` replacing 'A:A' with the column containing your data, and '34678' with the specific employee number you are searching for.
Alternatively, if the employee number is stored in another cell (like E2), use the formula `=COUNTIF(A:A, E2)`.
Hit Enter on your keyboard to execute the formula and view the total count of that specific name or employee number.

Create a PivotTable for a Complete Summary of Duplicates
A PivotTable is ideal when you need a full report showing the count of every employee name or number at once.
Easily Count Duplicates Using WPS Spreadsheet
WPS Spreadsheet supports all advanced Excel functions, including COUNTIF and powerful PivotTables, allowing you to accurately track employee numbers, summarize duplicates, and analyze data for free.
- 1. Open data file in WPS: Launch WPS Spreadsheet and open the document containing your employee data.
- 2. Use COUNTIF function: To count specific duplicates, enter `=COUNTIF(A:A, "Your criteria")` in an empty cell.
- 3. Insert a PivotTable: For a full report, go to Insert > PivotTable and select your data range.
- 4. Drag fields to summarize: Drag your target field into both the Rows and Values areas to instantly count duplicates.

Frequently Asked Questions
Why is COUNTIF returning 0 when the employee number is clearly in the list?
This usually happens due to formatting mismatches or hidden spaces. Ensure both your criteria cell and the data column are formatted identically (e.g., both as Text or both as Numbers). You can also use the TRIM function to remove accidental spaces in your data.
Can I count how many times an employee number appears with multiple conditions?
Yes, you can use the COUNTIFS function, which allows you to set multiple criteria across different ranges. For example, `=COUNTIFS(A:A, E2, B:B, "Present")` will count the occurrences of the employee number in column A only if column B says 'Present'.
Why does my PivotTable show 'Sum of Employee Number' instead of 'Count'?
Excel and WPS Spreadsheet automatically default to 'Sum' when the data in the Values area consists of numbers. To fix this, click on the field in the Values area, select 'Value Field Settings', and change the summary function from 'Sum' to 'Count'.




