Pivot Table Issues

How to Count Distinct Job Types per Customer in Excel Pivot Tables

Tauseeq Magsi Sep 28, 2026 869 views

Question details

Calculate the exact number of unique job types associated with each customer, ensuring that repeated jobs of the same type are only counted once.

How to Count Distinct Job Types per Customer in an Excel Pivot Table

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing customer job data using PivotTables to understand the variety of services utilized by each client.
Observed behavior: A standard PivotTable counts every single instance of a completed job, resulting in inflated numbers due to duplicate job types being tallied multiple times.

Before you start

Ensure your dataset contains clear column headers and no blank rows. If you plan to use the distinct count feature in PivotTables, verify that you are using a version of Excel that supports the Data Model (Excel 2013 for Windows or newer).

Solution 1Recommended

Use the Data Model to Calculate Distinct Count in a PivotTable

Adding your data to the Data Model allows you to access the hidden 'Distinct Count' calculation option directly within the PivotTable settings.

This method is the most seamless way to handle unique counts within standard PivotTable reporting. It requires no complex formulas, though it does rely on the Data Model feature.

Insert a Data Model PivotTable

Highlight your source data, navigate to the Insert tab, and click PivotTable. In the creation dialog box, check the box labeled 'Add this data to the Data Model' and click OK.

Set Up the PivotTable Fields

In the PivotTable Fields pane, drag the Customer field into the Rows area, and drag the Job Type field into the Values area.

Access Value Field Settings

Click the drop-down arrow next to Job Type in the Values area and select Value Field Settings.

Apply Distinct Count

In the 'Summarize value field by' list, scroll all the way to the bottom, select 'Distinct Count', and click OK to apply the changes.

Use the Data Model to Calculate Distinct Count in a PivotTable

Data Model Limitation: The 'Add this data to the Data Model' option is not available in older versions of Excel or certain Mac versions. If you do not see this option, use the formula method below.

Solution 2

Calculate Distinct Count Using UNIQUE and FILTER Formulas

If you are using Microsoft 365 or a newer spreadsheet application, you can extract a distinct count dynamically without relying on PivotTables.

WPS Spreadsheet

Easily Analyze Data and Count Distinct Values with WPS Office

WPS Spreadsheet offers powerful data analysis tools and full support for dynamic array formulas, making it incredibly simple to calculate distinct counts without heavy data models.

1. Open Your Data: Launch WPS Spreadsheet and open your existing dataset containing customer and job type records.
2. List Unique Customers: Use the Remove Duplicates feature or the UNIQUE formula to generate a clean list of your customers.
3. Apply the Distinct Count Formula: Use the combination of COUNTA, UNIQUE, and FILTER formulas to instantly calculate the unique job types per customer.
4. Format and Export: Easily format your results into a clean table and save it natively in .xlsx format for seamless sharing.

Fully compatible with Microsoft Excel file formats (.xlsx and .xls).Supports advanced dynamic array formulas like UNIQUE, FILTER, and COUNTA.Lightweight, fast performance for large datasets and complex calculations.Free to use with a familiar, user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable count duplicates by default?

By default, when you place a text field into the Values area of a PivotTable, Excel uses the 'Count' function. This function simply tallies every row where data exists, meaning duplicate entries are counted multiple times instead of being consolidated.

Why is the Distinct Count option missing from my PivotTable settings?

The Distinct Count option relies on the OLAP engine and is only available if you check 'Add this data to the Data Model' when creating the PivotTable. If you skipped this step or are using an older version of Excel that doesn't support the Data Model, the option will not appear.

Can I do a distinct count in a PivotTable without the Data Model?

Yes, but you will need a helper column in your source data. You can add a new column with a formula like =1/COUNTIFS(CustomerColumn, [@Customer], JobTypeColumn, [@JobType]). Then, create a standard PivotTable and set this helper column to 'Sum' in the Values area.

Excel & Data Problems