Pivot Table Issues

How to Count Distinct Job Titles by Province in an Excel PivotTable

Kushani Nimanthika Oct 9, 2026 869 views

Question details

The user needs a method to calculate the number of unique job titles within each province using an Excel PivotTable, rather than a raw count of all employee records.

How to Count Distinct Job Titles by Province in an Excel PivotTable

Product: Microsoft Excel
Device & OS: not provided
Scenario: A user is analyzing regional employee data and wants to identify the exact variety of roles in different locations.
Observed behavior: By default, the PivotTable counts every instance of a job title, resulting in duplicate entries being included. The goal state is to extract a distinct count of unique titles per province.

Before you start

Ensure your dataset contains clear column headers for 'Province' and 'Job Title', and verify that your version of Excel supports the Data Model functionality required for distinct counting.

Solution 1Recommended

Use the Data Model to Calculate Distinct Count in PivotTables

By adding your raw data to Excel's Data Model during the PivotTable creation, you unlock the ability to summarize values by their distinct count.

Standard PivotTables only offer a basic 'Count' function which counts every row, including duplicates. To count unique instances, such as different job titles within a single province, the data must first be processed through the Excel Data Model.

Select Data and Insert PivotTable

Highlight your entire dataset, including the headers. Go to the 'Insert' tab on the top ribbon and click on 'PivotTable'.

Enable the Data Model

In the 'Create PivotTable' dialog box, choose where you want the PivotTable to be placed. Before clicking OK, you must check the box at the bottom that says 'Add this data to the Data Model'.

Arrange PivotTable Fields

Once the PivotTable pane opens on the right, drag the 'Province' field into the 'Rows' area. Next, drag the 'Job Title' field into the 'Values' area.

Change to Distinct Count

By default, the Values area will show 'Count of Job Title'. Click on this item in the Values area and select 'Value Field Settings'. Scroll to the very bottom of the 'Summarize Values By' list, choose 'Distinct Count', and click 'OK'.

Use the Data Model to Calculate Distinct Count in PivotTables

Verification Step: Your PivotTable will now display only the number of unique job titles present in each province, ignoring any duplicate entries for the same role.

Powerful Data Analysis

Simplify Complex Data Summaries with WPS Spreadsheet

WPS Office provides highly capable PivotTable functionalities to help you seamlessly summarize datasets, categorize regional statistics, and quickly analyze unique records without the steep learning curve.

1. Open Data: Launch WPS Spreadsheet and open your existing data file containing the province and job records.
2. Insert PivotTable: Select the data range, navigate to the 'Insert' tab, and choose 'PivotTable' to begin data organization.
3. Configure Fields: Drag 'Province' to the Rows area and configure the 'Job Title' field in the Values area to easily extract the insights you need.

Intuitive PivotTable creation to organize and analyze data effortlesslyFully compatible with Microsoft Excel (.xlsx, .xls) files and structuresLightweight architecture ensures smooth operation on large datasetsFree alternative with a familiar, easy-to-navigate user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is 'Distinct Count' missing from my Value Field Settings?

The 'Distinct Count' option is exclusive to PivotTables that use the Data Model. If you did not check the 'Add this data to the Data Model' box when initially creating the PivotTable, this option will not appear. You will need to delete the current PivotTable and create a new one with that box checked.

Does distinct count update automatically when I add new employees?

PivotTables do not update automatically. Whenever you add new rows of employee data, you must right-click anywhere inside your PivotTable and select 'Refresh' to update the distinct count.

Can I count unique job titles using a formula instead?

Yes, if you prefer using formulas and have a newer version of Excel, you can combine the UNIQUE and COUNTA functions. Alternatively, you can use an array formula with SUM and COUNTIF to achieve a distinct count without utilizing a PivotTable.

Excel & Data Problems