# Pivot Table – Count Unique Values in Excel and Google Sheets

Written by

Reviewed by

*This tutorial demonstrates how to count unique values with a pivot table in Excel and Google Sheets.*

## Count Distinct Values in a Pivot Table

Consider the following table of data. Say you want to count the number of salespeople. Since each person has multiple records in the dataset, you need to make sure your count doesn’t include duplicates.

- In the
**Ribbon**, go to**Insert > PivotTable**.

- Make sure that
**Add this data to the Data Model**is checked, and then click**OK**.

- Now drag the
*Product*field down to the**Rows**area, and the*Salesperson*field down to the**Values**area.

- In the drop down next to the count of the
*Salesperson*field, choose**Value Field Settings**.

- From the
**Summarize value field by**list, choose**Distinct Count**, and then click**OK**.

The data is changed to show how many salespeople sold a particular product.

**Tip**: Try using some shortcuts when you’re working with pivot tables.

## Count Unique Values in Google Sheets Pivot Table

Counting unique values can done in Google Sheets with a pivot table too, but it’s done a bit differently. Say, again, that you want to count the number of salespeople in the dataset (partially) pictured below.

- First, create a pivot table, adding
*Region*as a**Row**and the*Salesperson*as a**Value**. As the*Salesperson*field isn’t made up of numeric figures, the pivot table editor automatically adds a**COUNTA**summary for that field.

- In the
**Summarize by**drop down, change the function from COUNTA to COUNTUNIQUE.

Now the data only shows how many salespeople are in each specific region.

**Note**: Although it doesn’t apply to this example, the **Grand Total** may not equal the sum of the row values. If one salesperson worked in both *California* and *Florida*, for example, they would be counted once in Row 2, once in Row 3, and once in Row 7; the sum of B2:B6 would be 10, but the grand total would still be 9.