Pivot Table Issues

How to Create Subtotals and Employee Counts by Name in WPS Spreadsheets

Algirdas Jasaitis Nov 13, 2026 869 views

Question details

User wants to summarize payroll-style data where employees appear on multiple rows for different pay periods, producing gross pay totals and employee or worker code counts by name.

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: The user has a multi-row employee or payroll list and needs a clear report by employee name.
Observed behavior: Multiple rows per employee make it hard to see per-name gross pay totals and worker code counts without a PivotTable or subtotal report.

Before you start

Confirm whether Count should mean the number of pay-period rows, worker codes, or unique employees, and ensure the source range includes a header row such as Name, Gross Pay, and Worker Code.

Solution 1Recommended

Create a PivotTable with Sum and Count values

Use this when you need a fast, no-formula report that totals Gross Pay by employee name and counts worker code or record entries.

A PivotTable keeps the original data unchanged and lets you rearrange Name, Gross Pay, and Worker Code without writing formulas.

Place Name in Rows, Gross Pay in Values as Sum, and Worker Code in Values as Count to produce a payroll-style summary.

Select the source data

Highlight the data range including the Name, Gross Pay, and Worker Code header row, or click inside the data list.

Insert a PivotTable

Go to Insert > PivotTable. Choose New Worksheet and confirm the selected range, then click OK.

Add the row field

In the PivotTable Field List, drag Name to the Rows area so each employee appears once.

Summarize gross pay

Drag Gross Pay to the Values area. Click the value field and choose Value Field Settings, then select Sum.

Count worker codes or records

Drag Worker Code to the Values area. Click the value field and choose Value Field Settings, then select Count. Rename the value headers if needed.

Unique employee count: If Count should be unique worker codes or unique employees, use the Distinct Count method instead of a normal Count.

Solution 2

Count unique worker codes with Distinct Count

Use this when the report must count each worker code or employee once even if the code appears on multiple pay-period rows.

Solution 3

Add subtotals by employee name without a PivotTable

Use this when you prefer a sorted, outlined list with a subtotal row under each employee instead of a separate summary table.

WPS Spreadsheets

Summarize Employee Pay Data with Subtotals and Counts in WPS Spreadsheets

Use WPS Spreadsheets to build PivotTable reports that sum gross pay and count worker codes by employee name. You can also apply Data > Subtotal or use Distinct Count for unique values, then save the file as .xlsx for Excel compatibility.

1. Open WPS Spreadsheets and load the employee data: Launch WPS Office, open the payroll workbook or paste the data into a new sheet with headers: Name, Gross Pay, Worker Code.
2. Insert a PivotTable report: Select the data range and click Insert > PivotTable. Choose New Worksheet and confirm the range includes the header row.
3. Add fields to summarize by name: Drag Name to Rows, Gross Pay to Values and set it to Sum, then drag Worker Code to Values and set it to Count.
4. Use Distinct Count or Subtotals if required: For unique worker codes, create the PivotTable through the Data Model and select Distinct Count. For a sorted list instead of a PivotTable, sort by Name and click Data > Subtotal.
5. Format and save as an Excel-compatible file: Adjust number formatting and column labels, then click Menu > Save As and choose .xlsx so the report opens in Microsoft Excel.

Create PivotTables with Sum, Count, and Distinct Count in WPS SpreadsheetsSummarize pay-period data by employee name without complex formulasOpen, edit, and save .xlsx files with Microsoft Excel compatibilityFree and lightweight Office suite with a familiar ribbon interfaceKeep original data intact while producing clear subtotal and count reports

microsoft office alternative - wps office

Frequently Asked Questions

How do I count unique worker codes instead of the number of rows in WPS Spreadsheets?

Create a PivotTable with 'Add this data to the Data Model' enabled, add Worker Code to Values, open Value Field Settings, and choose Distinct Count.

Can I subtotal gross pay by employee name without building a PivotTable?

Yes. Sort the data by Name, click Data > Subtotal, choose Name in 'At each change in', set the function to Sum, and select Gross Pay.

Why does my PivotTable count records instead of unique employees?

A normal Count value field counts non-empty cells in the source rows. To count each employee once, use Distinct Count, or add Employee ID or Worker Code to the Data Model and apply Distinct Count.

Does WPS Spreadsheets support the same PivotTable Sum and Count workflow as Excel?

Yes. WPS Spreadsheets supports PivotTables with Sum, Count, and Distinct Count value fields and can open and save .xlsx files.

How do I keep employee subtotals updated when I add new pay-period rows?

Use a PivotTable and refresh it by clicking Data > Refresh All or right-clicking the PivotTable and choosing Refresh after adding rows. If you convert the source range to a table, new rows are included automatically.

Excel & Data Problems