The pivot table shown is based on two fields: State and Color. The State field is configured as a row field, and the Color field is a value field, as seen below.

In the Pivot Table, the Color field has been renamed “Colors”, and “Summarize values by” has been set to “Distinct count”:

Data model

When the Pivot Table is created, the “Add this data to the Data Model” box is checked. This is what makes the distinct count option available.




Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.   

Pivot table unique count - 27Pivot table unique count - 38Pivot table unique count - 4