Count Unique Customers per Broker in an Excel PivotTable
Question details
Calculate the number of distinct customers served by each broker, even when customers appear in multiple transaction or category rows.

- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- A transaction table contains broker names and customer names, with some broker-customer pairs repeated across rows.
- Observed behavior
- The desired summary shows each broker and counts each customer only once for that broker.
Check that the source data has a header row and separate columns for broker and customer names. Make names consistent and decide whether blank customer names should be excluded.
Use Distinct Count in an Excel PivotTable
Use the Data Model to count each customer once per broker in a PivotTable.
Select a cell in the source data, choose Insert > PivotTable, and confirm the table or range.
In the Create PivotTable dialog, select Add this data to the Data Model, choose a destination, and click OK.
In the PivotTable Fields pane, drag Broker Name to Rows and Customer Name to Values.
Open the Customer Name value field menu, select Value Field Settings, choose Distinct Count, and click OK.
If customer names contain blanks, add Customer Name to the Filters area and clear the blank item in the filter.

Count unique customers with a Microsoft 365 formula
Use a formula when you want a per-broker count outside a PivotTable.
Summarize unique customers by broker in WPS Spreadsheets
Use a helper column to mark the first occurrence of each broker-customer pair, then sum those markers in a WPS Spreadsheets PivotTable.
- 1. Open the source workbook: Open the workbook in WPS Spreadsheets and identify the broker and customer columns.
- 2. Add a helper column: Add a header such as Unique Customer Flag. If broker names are in column A and customer names are in column B, enter =IF(OR(A2="",B2=""),0,IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,1,0)) in the helper column on row 2.
- 3. Fill the formula down: Drag the helper cell's fill handle to the last data row. The formula marks the first occurrence of each nonblank broker-customer pair with 1.
- 4. Create the PivotTable: Select the source table including the helper column, choose Insert > PivotTable, and confirm the source range and destination.
- 5. Summarize by broker: Drag the broker field to Rows and Unique Customer Flag to Values. Set the value calculation to Sum if it is not already selected.

Frequently Asked Questions
Why does my PivotTable show Count instead of Distinct Count?
A regular Count includes repeated customer entries. To access Distinct Count, create the PivotTable through Insert > PivotTable and select Add this data to the Data Model before choosing Distinct Count in Value Field Settings.
Does the same customer count for more than one broker?
Yes. When Broker Name is in Rows, a customer associated with two brokers counts once under each broker.
How do I exclude blank customer names?
In the PivotTable, add Customer Name to Filters and clear the blank item. The Microsoft 365 formula and WPS helper formula in this guide also exclude blank customer names.
How do I update the counts after adding transactions?
Ensure the new rows are included in the PivotTable source, then right-click the PivotTable and choose Refresh. If you use the WPS helper-column method, fill the helper formula into the new rows before refreshing.




